With MySQL, how can I generate a column containing the record index in a table?

Question

Is there any way I can get the actual row number from a query?

I want to be able to order a table called league_girl by a field called score; and return the username and the actual row position of that username.

I'm wanting to rank the users so i can tell where a particular user is, ie. Joe is position 100 out of 200, i.e.

User Score Row Joe  100    1 Bob  50     2 Bill 10     3 

I've seen a few solutions on here but I've tried most of them and none of them actually return the row number.

I have tried this:

SELECT position, username, score FROM (SELECT @row := @row + 1 AS position, username, score         FROM league_girl GROUP BY username ORDER BY score DESC)  

As derived

...but it doesn't seem to return the row position.

Any ideas?

Answer 1

You may want to try the following:

SELECT  l.position,          l.username,          l.score,         @curRow := @curRow + 1 AS row_number FROM    league_girl l JOIN    (SELECT @curRow := 0) r; 

The JOIN (SELECT @curRow := 0) part allows the variable initialization without requiring a separate SET command.

Test case:

CREATE TABLE league_girl (position int, username varchar(10), score int); INSERT INTO league_girl VALUES (1, 'a', 10); INSERT INTO league_girl VALUES (2, 'b', 25); INSERT INTO league_girl VALUES (3, 'c', 75); INSERT INTO league_girl VALUES (4, 'd', 25); INSERT INTO league_girl VALUES (5, 'e', 55); INSERT INTO league_girl VALUES (6, 'f', 80); INSERT INTO league_girl VALUES (7, 'g', 15); 

Test query:

SELECT  l.position,          l.username,          l.score,         @curRow := @curRow + 1 AS row_number FROM    league_girl l JOIN    (SELECT @curRow := 0) r WHERE   l.score > 50; 

Result:

+----------+----------+-------+------------+ | position | username | score | row_number | +----------+----------+-------+------------+ |        3 | c        |    75 |          1 | |        5 | e        |    55 |          2 | |        6 | f        |    80 |          3 | +----------+----------+-------+------------+ 3 rows in set (0.00 sec)