I move the blog to my new domain, please visit : http://jimmod.com/blog/2008/09/displaying-row-number-rownum-in-mysql/ for latest update
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.
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 :)