SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Location
    sudo rm -rf /
    Posts
    386
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to select next and previous items with order by

    Hello,

    I need to select the previous and next items. I am using this:
    DO @a:="title_of_current_item";
    SELECT * from tabled where itemtitle < @a order by itemtitle desc limit 0,5; --previous items
    SELECT * from tabled where itemtitle > @a order by itemtitle asc limit 0,5; --next items

    is there better (one query) and/or faster way?

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, there is a better way, please just show your query that gets the current item and i;ll show you how to fix it

    also, may i ask why you are retrieving complete rows for the 5 prev and 5 next rows?

    typically what people want is the id and title of the (single) next and prev rows, for linking purposes

    for example, <a href="page.php"id=$next>$nexttitle</a>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2008
    Location
    sudo rm -rf /
    Posts
    386
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need the complete rows because I need the title for the next/previous link and other options too.
    The query for the current item is:
    SELECT * from tabled where id='9';

    Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please try this --
    Code:
    SELECT * 
      from tabled 
     where id IN 
           ( 9
           , ( SELECT id
                 FROM tabled
                WHERE itemtitle <
                      ( SELECT itemtitle
                          FROM tabled
                         WHERE id = 9 )
               ORDER
                   BY itemtitle DESC )
           , ( SELECT id
                 FROM tabled
                WHERE itemtitle >
                      ( SELECT itemtitle
                          FROM tabled
                         WHERE id = 9 )
               ORDER
                   BY itemtitle ASC )
           )
    ORDER
        BY itemtitle ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jul 2008
    Location
    sudo rm -rf /
    Posts
    386
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thats 4 subqueries, will be faster? But I need other columns too, almost all.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, that's only 3 subqueries, and the query returns all columns

    did you try it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jul 2008
    Location
    sudo rm -rf /
    Posts
    386
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get this error :
    Subquery returns more than 1 row

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aw crap, and i forgot the LIMITs as well

    okay, let's try a slight change...
    Code:
    SELECT * 
      from tabled 
     WHERE id = 9
        OR id IN
           ( 
             ( SELECT id
                 FROM tabled
                WHERE itemtitle <
                      ( SELECT itemtitle
                          FROM tabled
                         WHERE id = 9 )
               ORDER
                   BY itemtitle DESC LIMIT 5 )
             UNION ALL
             ( SELECT id
                 FROM tabled
                WHERE itemtitle >
                      ( SELECT itemtitle
                          FROM tabled
                         WHERE id = 9 )
               ORDER
                   BY itemtitle ASC LIMIT 5 )
           )
    ORDER
        BY itemtitle ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jul 2008
    Location
    sudo rm -rf /
    Posts
    386
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    got an error at union all.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what did the error message say? i'll bet your version of mysql doesn't allow LIMIT in a subquery

    okay, here's an alternate strategy

    first, run this query --
    Code:
    SELECT id
      FROM tabled
     WHERE itemtitle <
           ( SELECT itemtitle
               FROM tabled
              WHERE id = 9 )
    ORDER
        BY itemtitle DESC LIMIT 5
    next, run this query --
    Code:
              
    SELECT id
      FROM tabled
     WHERE itemtitle >
           ( SELECT itemtitle
               FROM tabled
              WHERE id = 9 )
    ORDER
        BY itemtitle ASC LIMIT 5
    now take the results of those two queries, which will be 10 (or fewer) id numbers, and add them to this query --
    Code:
    SELECT * 
      from tabled 
     WHERE id IN ( 9 , list your 10 ids here )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Jul 2008
    Location
    sudo rm -rf /
    Posts
    386
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql version 5.1.31, and thanks for everything.

  12. #12
    SitePoint Addict
    Join Date
    Jul 2008
    Location
    sudo rm -rf /
    Posts
    386
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 now I have another problem with my queries, when @a is '' or '0' the queries for next and previous items are empty. Apart from that other queries do return the previous and next items.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by buldozerce View Post
    when @a is '' or '0'
    why would you have '' or '0' as the title of the current item?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    Join Date
    Jul 2008
    Location
    sudo rm -rf /
    Posts
    386
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's not only the title , I also order items by weightnumber and date added. Some items don't have date added or have weight number of 0

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, well, i don't know what to say to you

    how can you expect to run the query based on next and previous dates if the current item doesn't have a date???

    if the current item doesn't have a date then next and previous dates might be a bit problematic, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Jul 2008
    Location
    sudo rm -rf /
    Posts
    386
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes but mysql do order them by date even if they don't have dates:
    select * from tabled order by date_added


Bookmarks

Posting Permissions

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