Search code examples
mysqldatabaseranking

MySQL sort adding progressive id

i have a problem

i want to sort my table content with MySQL, and use results as ranking table in a game:

scores: user | points

i have this query: SELECT * FROM scores ORDER BY score DESC LIMIT 0,10

how can i add a column in my query result to display every record with a progressive number?

i.e.

what my query is displaying:

mike        8000
al          7569
frank       7296
alex        7000
dana        6237
mark        6201
gankz       5766
mickeymouse 5420
donaldduck  5126
user        2569

what i want it to display:

**1** mike 8000
**2** al 7569
**3** frank 7296
**4** alex 7000
**5** dana 6237
**6** mark 6201
**7** gankz 5766
**8** mickeymouse 5420
**9** donaldduck 5126
**10** user 2569
Solution

  • Try

    set @row_num = 0; 
SELECT @row_num := @row_num + 1 as row_index, user, points 
FROM scores ORDER BY score DESC LIMIT 0,10