MySQL Rank Not Matching High Score in Table

Question

While making a game the MySQL call to get the top 10 is as follows:

SELECT username, score FROM userinfo ORDER BY score DESC LIMIT 10

This seems to work decently enough, but when paired with a call to get a individual player's rank the numbers may be different if the player has a tied score with other players. The call to get the players rank is as follows:

SELECT (SELECT COUNT(*) FROM userinfo ui WHERE (ui.score, ui.username) >= (uo.score, uo.username)) AS rank FROM userinfo uo WHERE username='boddie';

Example results from first call:

+------------+-------+
| username   | score |
+------------+-------+
| admin      |  4878 |
| test3      |  3456 |
| char       |   785 |
| test2      |   456 |
| test1      |   253 |
| test4      |    78 |
| test7      |     0 |
| boddie     |     0 |
| Lz         |     0 |
| char1      |     0 |
+------------+-------+

Example results from second call

+------+
| rank |
+------+
|   10 |
+------+

As can be seen, the first call ranks the player at number 8 on the list, but the second call puts him at number 10. What changes or what can I do to make these calls give matching results?

Thank you in advance for any help!

You are effectively sorting by score desc AND username desc within that, hence boddie comes lower than Lz and char1 — Kickstart, Jul 01 '13 at 16:11
Yah I am beginning to see the mistake after looking at all the great responses below. There is so many good ones haha — boddie, Jul 01 '13 at 16:24

score 2 · Answer 1 · answered Jul 01 '13 at 16:03

2

I would change your Order by to include username, so that you always get the same order. So it would look like:

... ORDER BY score DESC, username ASC ...

answered Jul 01 '13 at 16:03

BryPie

367
2
6

score 2 · Accepted Answer · answered Jul 01 '13 at 16:03

2

You need in the first query to :

ORDER BY 
   score DESC,
   username DESC

This way it will reach at rank 10... this is due to the username comparison in the second query :

(ui.score, ui.username) >= (uo.score, uo.username)

answered Jul 01 '13 at 16:03

Stephan

8,000
3
36
42

Upvote since we had pretty much the same answer in the same minute, but probably want names in ascending alphabetical order, not descending :) – BryPie Jul 01 '13 at 16:05
@BryPie you can do that by changing the second query – Stephan Jul 01 '13 at 16:06
ended up using this due to simplicity and it worked with what I had quite well. Thank you! – boddie Jul 08 '13 at 14:19

kcsoft · Answer 3 · 2013-07-01T16:14:09.950

1

SELECT
    uo.username,
    (SELECT COUNT(*)+1 FROM userinfo ui WHERE ui.score>uo.score) AS rank
FROM userinfo uo
WHERE uo.username='boddie'

Or if you need to get username, score and ranks:

SELECT
    uo.username,
    uo.score,
    (@row := @row + 1) AS rank
FROM userinfo uo
JOIN (SELECT @row := 0) r
ORDER BY uo.score DESC, uo.username ASC

You could add

HAVING uo.username = 'boddie'

to get only one user

edited Jul 01 '13 at 16:14

answered Jul 01 '13 at 16:05

kcsoft

2,917
19
14

in ur case all users that have score = 0 they will have the same rank :) – Stephan Jul 01 '13 at 16:09
yeah but you need a way do differentiate them , like the OP tried – Stephan Jul 01 '13 at 16:12
Not sure using HAVING for your 2nd answer is safe due to the possible ways that MySQL can chose to optimise queries using user variable – Kickstart Jul 01 '13 at 16:19

score 1 · Answer 4 · answered Jul 01 '13 at 16:12

Another way of doing it:-

SELECT UsersRank
FROM 
(
    SELECT userinfo.score, userinfo.username, @Rank:=@Rank+1 AS UsersRank
    FROM userinfo 
    CROSS JOIN (SELECT (@Rank:=0)) Sub1
    ORDER BY userinfo.score DESC, userinfo.username
) Sub2
WHERE username = 'boddie'

score 0 · Answer 5 · edited Jul 01 '13 at 16:37

0

Try this.

SELECT (COUNT(*)+1) AS rank
FROM   userinfo ui
WHERE  ui.score > (SELECT score
                   FROM   userinfo
                   WHERE  username='boddie');

edited Jul 01 '13 at 16:37

mishik

9,973
9
45
67

answered Jul 01 '13 at 16:18

Nitesh Mishra

311
1
10

MySQL Rank Not Matching High Score in Table

5 Answers5