MySQL Rank Not Matching High Score in Table
- by boddie
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!
