Displaying row number (rownum) in MySQL
by jimmy on Sep.09, 2008, under My Life
Sometimes you need to do query with MySQL for reporting.
And you’ll need to display the row number/ranking in query result.
Example you have this table:
table : player
fields : playerid, name & score
For reporting purpose you need to query the top 10 (highest score).
The result should be : rank, memberid, name, score.
Example :
rank playerid name score
———————————–
1 A1029 Jimmy 100
2 A9830 Lia 98
3 B28d0 Lulu 90
…
…
10 B8789 Lele 50
Now you can easily query the top 10 by using ‘limit’ and ‘order by’, but how to automatically add row number in query result?
Here’s how you do it:
select @rownum:=@rownum+1 ‘rank’, p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10;
Try it
This will create a variable rownum, initialize with value 0 & increase it by 1 for every record
————————————————————————————————————————————
One of the comment asking different rule for ranking (Thank to @justin below that raising this issue).
He want players that have same score should have same ranking.
So the result should be like:
rank playerid name score
———————————–
1 A1029 Jimmy 100
2 A9830 Lili 90
2 B28d0 Lulu 90
3 X9830 Lolo 70
3 Y28d0 Lele 70
After testing some query I find a way to do this by having a variable that save the score then compared on next record.
If the score is the same the rank will not changed, if it’s not the same (that mean the score is less then previous – because it’s ordered desc) the rank will be increased by 1.
Here’s the query:
select IF(@score=p.score, @rownum, @rownum:=@rownum+1) rank2, p.*, (@score:=p.score) dummy from player p, (SELECT @rownum:=0) x, (SELECT @score:=0) y order by score desc limit 10;
————————————————————————————————————————————
Technorati Tags: mysql, select, query, rownum, row number, rank
Related posts:
55 Comments for this entry
2 Trackbacks / Pingbacks for this entry
-
Getting the current row number from MySQL « Dominic O’Connor
April 22nd, 2009 on 10:02 pm[...] the credit goes to this blog post, but I feel it’s always good to propagate tips like this. MORE [...]
-
Membuat No di Hasil Query Mysql « Pengingat
February 24th, 2012 on 12:35 am[...] link dari situs ini dan ini cara membuat no pada hasil query [...]
February 26th, 2012 on 4:25 am
Thank you, it saved me a lot of time. But you can mention that when you use stored procedure with order by, it doesn´t work properly.
April 19th, 2012 on 7:03 am
@CV: I am not sure what you meant by stored procedure, but if you were talking about inner queries (sub-queries), then it does work, as it is my case.
I have 2 sub-queries and both are using order by. I have just put a 3rd query on top of them with @rownum and it worked. Cheers.
April 26th, 2012 on 10:38 pm
It works, thanks…
May 8th, 2012 on 3:56 am
Why can I not select a single record with:
(SELECT @row_number:=0); SELECT row_number FROM (SELECT userid,@row_number:=@row_number+1 AS row_number FROM users ORDER BY `characters` asc) sub1 WHERE userid = 1
What am I doing wrong?
May 19th, 2012 on 7:40 am
Hi,
Thanks for this piece of code. it works just fine.
However, my other requirement is to rank within a group.
For example, I have data as follows
Model Color Sales
Mazda Black 15
Mazda Red 25
Mazda White 35
Honda Black 22
Honda Red 18
Honda White 43
How Can I rank within the Model? So for Mazda i need to show 1,2and 3 and same for Honda as well