Adding row numbers help

ozzii · #1 (**permalink**) 05-24-10, 13:12

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.

r937 · #2 (**permalink**) 05-24-10, 13:34

there's no such thing as row numbers

you can have rank, though, but it'll cost ya in performance

DevilsAdvocate · #3 (**permalink**) 05-24-10, 14:09

Quote:

Originally Posted by ozzii

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

ozzii · #4 (**permalink**) 05-24-10, 14:42

Quote:

Originally Posted by r937

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?

r937 · #5 (**permalink**) 05-24-10, 15:16

Quote:

Originally Posted by ozzii

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

ozzii · #6 (**permalink**) 05-24-10, 16:42

Quote:

Originally Posted by r937

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.

gvee · #7 (**permalink**) 05-24-10, 17:16

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!

ozzii · #8 (**permalink**) 05-24-10, 17:25

Quote:

Originally Posted by gvee

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?

r937 · #9 (**permalink**) 05-24-10, 17:31

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

gvee · #10 (**permalink**) 05-25-10, 03:45

I chose "id" for illustrative purposes only

Good shout on the LIMIT!

ozzii · #11 (**permalink**) 05-25-10, 10:34

Quote:

Originally Posted by r937

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech