Jimmy’s Blog

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? (continue reading…)