Finding the row number

[tester]

Hi,

I should know this really, but never mind.

I've got a select statement e.g:

SELECT column1 FROM table ORDER BY column1 desc

I need to find out what row number a value in column1 is in.

If the data returned was this:

value1
value2
value3
value4
value5
value6

I would need to know that value4 is on row 3 (as MySQL counts from 0).

I could do some PHP loops although I'm sure that there is a simple MySQL statement to do what I need.

Any idea's?


Thanks,

Rob

[KodeKrash]

 SELECT row_id_column, column1 FROM tbl_name ORDER BY column1 DESC

Then, your data will be returned as:

row_number  value
row_number  value
row_number  value

And you can still display only the column1 data, but you'll have the row number.

[tester]

Hi,

There isn't a row id column though, I'm looking to get MySQL to count what row it is on. Or is row_id_column a pre-made variable what MySQL uses to number each row?

Thanks,

Rob