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

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?

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)

SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo

Here comes the structure of template I used:

  select
          /*this is a row number counter*/
          ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
          as rownumber,
          d3.*
  from 
  ( select d1.* from table_name d1 ) d3

And here is my working code:

select     
           ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
           as rownumber,
           d3.*
from
(   select     year( d1.date ), month( d1.date ), count( d1.id )
    from       maindatabase d1
    where      ( ( d1.date >= '2013-01-01' ) and ( d1.date <= '2014-12-31' ) )
    group by   YEAR( d1.date ), MONTH( d1.date ) ) d3

You can also use

SELECT @curRow := ifnull(@curRow,0) + 1 Row, ...

to initialise the counter variable.

Assuming MySQL supports it, you can easily do this with a standard SQL subquery:

select 
    (count(*) from league_girl l1 where l2.score > l1.score and l1.id <> l2.id) as position,
    username,
    score
from league_girl l2
order by score;

For large amounts of displayed results, this will be a bit slow and you will want to switch to a self join instead.

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

Related

Recent Posts