Search code examples
phpmysqlranking

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
Solution

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