MySQL sort adding progressive id

Question

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

possible duplicate of [Row Rank in a MySQL View](http://stackoverflow.com/questions/1964811/row-rank-in-a-mysql-view) — Michael Berkowski, Dec 07 '12 at 14:00
In particular, see OMGPonies' answer in the linked question. — Michael Berkowski, Dec 07 '12 at 14:00
Also http://stackoverflow.com/questions/3333665/mysql-rank-function — Michael Berkowski, Dec 07 '12 at 14:02

George D · Accepted Answer · 2012-12-07T15:08:35.557

3

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

edited Dec 07 '12 at 15:08

answered Dec 07 '12 at 14:02

George D

2,327
3
20
26

score 1 · Answer 2 · answered Dec 07 '12 at 14:10

1

Try

SELECT TOP 10 CONVERT(INT, ROW_NUMBER() OVER (ORDER BY Points)) AS RowRank, Users, Points FROM Scores

answered Dec 07 '12 at 14:10

Imad S.

375
3
15

score 0 · Answer 3 · edited May 23 '17 at 10:34

0

Because I can't write comments yet, I would like to point out that when doing a select statement it is better practice to not use SELECT *. If you're only grabbing name and score, only select for those.

What is the reason not to use select *?

edited May 23 '17 at 10:34

Community

1
1

answered Dec 07 '12 at 14:08

ethree

1,582
2
14
21

MySQL sort adding progressive id

3 Answers3