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?

In SQL, row numbers are really not important. What you should do is add an auto increment primary key to your table. — simendsjo, Jun 27 '10 at 09:44
The primary key should NEVER a row identifier as they are not reliable for the actual row position. — TheBounder, Jun 27 '10 at 09:46
Besides, since row number would be a function of the score which I assume is not a static value, making it an auto incremented value (primary key or not) would not give the intended result. — kasperjj, Jun 27 '10 at 09:54
you might want to save the ugly for a custom function, see http://datamakessense.com/mysql-rownum-row-number-function/ — AdrianBR, Oct 20 '14 at 16:14

score 175 · Accepted Answer · answered Jun 27 '10 at 09:49

175

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)

answered Jun 27 '10 at 09:49

Daniel Vassallo

337,827
72
505
443

19

You can also initialize `@curRow` by replacing the `JOIN` statement with a comma, like this: `FROM league_girl l, (SELECT @curRow := 0) r` – Mike Jun 27 '10 at 14:12
2

@Mike: That's true. And that's probably neater as well. Thanks for sharing this tip. – Daniel Vassallo Jun 27 '10 at 21:55
@DanielVassallo Please tell me what is the last **`r`** at the end of `(SELECT @curRow := 0) ` **`r`** ? – Mohammad Naji Feb 27 '12 at 13:30
2

@smhnaji MySQL requires that every "derived table" is given a name. I decided to call it "r" :) ... It has little purpose in this case, but you'd normally use it to reference attributes of the derived table, as if it was a real table. – Daniel Vassallo Feb 27 '12 at 23:05
Thank you @DanielVassallo. I didn't know that in this case we can name a table without the keyword `AS`. – Mohammad Naji Feb 28 '12 at 04:27
where can I put the JOIN clause if I have WHERE, LIMIT, ORDER as well? – emeraldhieu Sep 30 '12 at 11:42
21

People should be aware that this row number is calculated *before* any ordering takes place, so the numbers may become jumbled up if the order changes the row order. – Grim... Nov 08 '12 at 14:20
7

Is there a way to get this row number calculated after the `ORDER BY` ? – Pierre de LESPINAY Nov 30 '12 at 12:02
@PierredeLESPINAY Use a subquery – JohnD Aug 05 '13 at 15:57
2

Is it possible to query specific row numbers? i.e. row_number 2 and 3 using your query above? – jhnferraris Dec 03 '13 at 03:30
or you can use a custom function http://datamakessense.com/mysql-rownum-row-number-function/ – AdrianBR Oct 20 '14 at 16:15

score 39 · Answer 2 · answered Aug 12 '10 at 17:36

39

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

answered Aug 12 '10 at 17:36

Peter Johnson

2,673
22
14

is there a way to do this so that the iterator column is an integer and not a decimal? – kraftydevil May 15 '20 at 05:36

score 7 · Answer 3 · answered Dec 12 '14 at 21:33

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

perfect, works like charm and template can be reused with subquery as parameter.. — Zavael, Jan 22 '15 at 15:35

score 4 · Answer 4 · answered Aug 21 '13 at 04:37

4

You can also use

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

to initialise the counter variable.

answered Aug 21 '13 at 04:37

Hearth

383
1
4
13

4

`@curRow` may still have a value from the last time you ran this query in the current session. – Bill Karwin Dec 17 '13 at 03:06
True, but only if you are re-querying on the same connection instance. Local variables are automatically disposed once the connection is closed. – Hearth Jan 16 '14 at 06:31
Yes, that's what I meant when I said "in the current session." – Bill Karwin Jan 16 '14 at 16:42

score 3 · Answer 5 · edited Jan 29 '20 at 11:56

If you just want to know the position of one specific user after order by field score, you can simply select all row from your table where field score is higher than the current user score. And use row number returned + 1 to know which position of this current user.

Assuming that your table is league_girl and your primary field is id, you can use this:

SELECT count(id) + 1 as rank from league_girl where score > <your_user_score>

score 3 · Answer 6 · edited Sep 05 '12 at 11:08

3

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.

edited Sep 05 '12 at 11:08

Taz

3,718
2
37
59

answered Mar 23 '12 at 15:52

ftzdomino

71
2

score 0 · Answer 7 · edited Feb 04 '20 at 06:49

I know the OP is asking for a mysql answer but since I found the other answers not working for me,

Most of them fail with order by
Or they are simply very inefficient and make your query very slow for a fat table

So to save time for others like me, just index the row after retrieving them from database

example in PHP:

$users = UserRepository::loadAllUsersAndSortByScore();

foreach($users as $index=>&$user){
    $user['rank'] = $index+1;
}

example in PHP using offset and limit for paging:

$limit = 20; //page size
$offset = 3; //page number

$users = UserRepository::loadAllUsersAndSortByScore();

foreach($users as $index=>&$user){
    $user['rank'] = $index+1+($limit*($offset-1));
}

score 0 · Answer 8 · answered Oct 22 '18 at 07:15

I found the original answer incredibly helpful but I also wanted to grab a certain set of rows based on the row numbers I was inserting. As such, I wrapped the entire original answer in a subquery so that I could reference the row number I was inserting.

SELECT * FROM 
( 
    SELECT *, @curRow := @curRow + 1 AS "row_number"
    FROM db.tableName, (SELECT @curRow := 0) r
) as temp
WHERE temp.row_number BETWEEN 1 and 10;

Having a subquery in a subquery is not very efficient, so it would be worth testing whether you get a better result by having your SQL server handle this query, or fetching the entire table and having the application/web server manipulate the rows after the fact.

Personally my SQL server isn't overly busy, so having it handle the nested subqueries was preferable.

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

8 Answers8

Linked

Related