Numbering Rows

[boy007]

I have a couple of tables that I query, but I wish to add an extra column in the query which numbers the result set from 0-* (note, I don't wish to find out how many results there are).

This is so that I can extract rows between two numbers for example:

...where rowcount BETWEEN 32 AND 40

and I'd get a result such as

rowcount column 1
32 name1
33 name2
34 name3
35 name4
etc.


I'm using MySql 3.x

Thanks.

 

[r937]

sorry, no can do the numbering

however, you might be interested in the LIMIT keyword in the SELECT statement

r937.com | rudy.ca

 

[PHV]

So, you want a ranking query ?
You should have a unique sort key in order to do this.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cheerio]

The following SQL works for me in SQL Server - it might need tweaking to make it ANSI:

select staff_no, surname, rank = (select count(*) from Employees where staff_No < E.Staff_No)
from Employees E
ORDER BY Staff_No

Obviously Staff_No is a key field.

 

[boy007]

Thanks guys. Valuable info gained, but doesn't look like I can get round this one in MySQL

 

[r937]

cheerio's query does work in mysql -- in the current version, in the version before that, and also in the version before that

you're on the great-grandfather version


r937.com | rudy.ca

 

[PHV]

I guess the correct syntax is:
select staff_no, surname, (select count(*) from Employees where staff_No < E.Staff_No) rank
from Employees E
ORDER BY Staff_No

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

oh yeah, thanks PHV, i overlooked the fact that cheerio's query used rank=(select...

which obvioulsy is sql server syntax

i was referring to the use of a subquery, which mysql does support starting with verion 4.1 (the grandfather of the current version)

r937.com | rudy.ca