Daniel Hernandez Daniel Hernandez - 4 months ago 18
SQL Question

mysql - wins ranking no tie

I have a problem in the ranking of each user is perfect, the problem is when the user "a" has the same result as the user "b"

My code

SELECT ID, Wins,
(SELECT COUNT(*)+1 FROM users WHERE Wins>x.Wins) AS rank_upper,
(SELECT COUNT(*) FROM users WHERE Wins>=x.Wins) AS rank_lower
FROM `users` x WHERE x.username='$_GET[user]'

ID wins Rank
1 23 1
2 17 2
3 17 2
4 10 3
5 10 3


I like it like this:

ID wins Rank
1 23 1
2 17 2
3 17 3
4 10 4
5 10 5

Answer

Comparing also id might work for you

SELECT ID, Wins, 
    (SELECT COUNT(*)+1 FROM users WHERE Wins>x.Wins or (Wins=x.Wins and id<x.id)) AS Rank 
 FROM `users` x WHERE x.username='$_GET[user]'
Comments