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

indexing, mysql

Solution

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)

Problem

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?

Original source