Daniel Hernandez Daniel Hernandez - 1 year ago 76
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 Source

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]'