Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Adding row numbers help

    Hi,

    Is there any way to get row numbers from a record set?

    I tried the following to add row numbers :

    Code:

    SELECT @i:=@i+1 as row_num, ....

    but this doesnt appear in sequence i.e. 1,2,3... if the SQL is sorted using an ORDER BY clause.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there's no such thing as row numbers

    you can have rank, though, but it'll cost ya in performance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by ozzii View Post
    Is there any way to get row numbers from a record set?
    You could add a field such as row_num (auto-inc) and use that
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    there's no such thing as row numbers

    you can have rank, though, but it'll cost ya in performance
    whats the difference between row and rank? would rank appear in sequence though if it was used in conjunction with an order by clause?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    would rank appear in sequence though if it was used in conjunction with an order by clause?
    can you be specific about what you want?

    it's possible to list a set of people in ascending sequence by their surname, for example, and at the same time give each person's rank by age

    but i'm not going to speculate until i know what you're really after
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    can you be specific about what you want?

    it's possible to list a set of people in ascending sequence by their surname, for example, and at the same time give each person's rank by age

    but i'm not going to speculate until i know what you're really after

    I am trying to implement a Next Prev navigation link on a detail page in PHP so that clicking on one of the links takes you to the next or previous record instead of the next or previous page.

    I can set up the navigation for the results page and I thought I had something figured that would work for the detail page using the code below.

    Code:

    SELECT @i:=@i+1 as row_num, ....

    However it all got screwed up when I added an order by clause to the SQL in which case row_num no longer appeared in sequence i.e. 1,2,3.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Next or previous record only?
    Code:
    SELECT Min(id) As next_record
    FROM   your_table
    WHERE  id > @current_id
    
    SELECT Max(id) As previous_record
    FROM   your_table
    WHERE  id < @current_id
    Make these derived tables and join them back to your_table to get the related columns!
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by gvee View Post
    Next or previous record only?
    Code:
    SELECT Min(id) As next_record
    FROM   your_table
    WHERE  id > @current_id
    
    SELECT Max(id) As previous_record
    FROM   your_table
    WHERE  id < @current_id
    Make these derived tables and join them back to your_table to get the related columns!
    am not sure what you mean here. Where does the id come from?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT id , title
      FROM daTable
    WHERE  sort_col < @current_value
    ORDER BY sort_col DESC
    LIMIT 1
    Code:
    SELECT id , title
      FROM daTable
    WHERE  sort_col > @current_value
    ORDER BY sort_col ASC
    LIMIT 1
    the sort order isn't always on id, george, the example makes more sense if you sort by some other column (e.g. sort people by age, not by id)

    also, usually you want some other column as well as the id, like a name or title, which means your suggestion of MIN and MAX works only if you also join back to the original table, which makes your solution even more complicated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I chose "id" for illustrative purposes only
    Good shout on the LIMIT!
    George
    Home | Blog

  11. #11
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    Code:
    SELECT id , title
      FROM daTable
    WHERE  sort_col < @current_value
    ORDER BY sort_col DESC
    LIMIT 1
    Code:
    SELECT id , title
      FROM daTable
    WHERE  sort_col > @current_value
    ORDER BY sort_col ASC
    LIMIT 1
    the sort order isn't always on id, george, the example makes more sense if you sort by some other column (e.g. sort people by age, not by id)

    also, usually you want some other column as well as the id, like a name or title, which means your suggestion of MIN and MAX works only if you also join back to the original table, which makes your solution even more complicated
    Thanks. The above is workable. The only problem I have now is that my sort_column is a DATETIME value for whne the record was created. Unfortunatley MySql DATETIME doesnt store milliseconds which somewhat limits the above solution if there are multiple records with exactly the same TIMESTAMP.

Posting Permissions

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