mysql - wins ranking no tie

Question

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

What makes you say that `ID 2` is `Rank 2` while `ID 3` is `Rank 3` when their win count is the same? — Eduard, Jul 25 '16 at 07:56
is just one example, I want the result of the user who has the same point is different ranking — Daniel Hernandez, Jul 25 '16 at 07:59
sql injection see [link](http://www.acunetix.com/blog/articles/preventing-and-fixing-sql-injection-vulnerabilities-in-php-applications/) — Drew, Jul 25 '16 at 09:57

score 0 · Accepted Answer · answered Jul 25 '16 at 07:59

0

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

answered Jul 25 '16 at 07:59

vercelli

4,717
2
13
15

it does not work. I make the call with this code to the result – Daniel Hernandez Jul 25 '16 at 08:03
@DanielHernandez - do NOT use `mysql_fetch_arry()` -- the whole `mysql_xxx()` API in PHP is obsolete. Use `mysqli_xxx()` or PDO library instead. Also, NEVER NEVER put `$_GET` variables directly into your query string. At the least, you must use proper escaping techniques, but better to use prepared statements. See http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php/ for more info – Simba Jul 25 '16 at 11:46
@simba hi, Could you give me an example of how it should be please – Daniel Hernandez Jul 26 '16 at 14:34
@DanielHernandez easiest way would be to point you toward a good quality tutorial -- see http://www.phptherightway.com/#databases – Simba Jul 26 '16 at 15:18

mysql - wins ranking no tie

1 Answers1