Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    16

    Unanswered: Query to add edited entries to the top of a paginated list

    Hello, I have a will paginated list of properties and I'm trying to find a way to display the most recently updated entries at the top of the list based on their most current modify date. The approach I'm taking is to use mysql UNION and here's my query code:

    Code:
    (SELECT properties.*, layouts.title FROM properties
     LEFT OUTER JOIN layouts ON layouts.property_id = properties.property_id 
     WHERE properties.property_id IN (147, 159 ) 
     ORDER BY properties.modify_date ASC LIMIT 10 OFFSET 50) 
     UNION ALL
     (SELECT properties.*, layouts.title FROM properties
     LEFT OUTER JOIN layouts ON layouts.property_id = properties.property_id
     WHERE properties.property_id>'0' ORDER BY properties.modify_date DESC) LIMIT 10 OFFSET 50
    This approach works perfectly when you're on the first page and the edited properties show up first ordered by the last edited, but when you're on any other page, the edited list doesn't appear on top.

    It seems like I'm asking quite a lot and I'm not sure this is even fully possible w/out adding some complicated hacks.

    Thanks,
    Clem C

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I think you will find that your final limit is causing your problem, try ORDER BY and LIMIT at the end.

    Code:
    (SELECT properties.*, layouts.title FROM properties
     LEFT OUTER JOIN layouts ON layouts.property_id = properties.property_id 
     WHERE properties.property_id IN (147, 159 ) 
     ORDER BY properties.modify_date ASC LIMIT 10 OFFSET 50) 
     UNION ALL
     (SELECT properties.*, layouts.title FROM properties
     LEFT OUTER JOIN layouts ON layouts.property_id = properties.property_id
     WHERE properties.property_id>'0' ORDER BY properties.modify_date DESC LIMIT 10 OFFSET 50)
    Previously you are limiting after the full results are returned which I don't think is what you are looking to achieve?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a UNION query should have only one ORDER BY clause

    i know mysql will let you write an ORDER BY clause for each SELECT, but that's non-standard

    what you should do is write just one SELECT with one ORDER BY clause

    that will mean re-thinking your approach

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •