MySQL – Get User Rank Handling Equal Scores

MySQLrankselectsorting

Imagine there is a table with users logins and their scores:

+----+-------+-------+
| id | login | score |
+----+-------+-------+
| 1  | log1  | 4     |
+----+-------+-------+
| 2  | log2  | 4     |
+----+-------+-------+
| 3  | log3  | 28    |
+----+-------+-------+
| 4  | log4  | 1     |
+----+-------+-------+
| 5  | log5  | 3     |
+----+-------+-------+

How to select the rank of a specific user (for example: "log2")?

The very important thing is that this selected rank shoud increase even in case of equal scores so every user will have a unique rank.

Example of how rank shoud be counted:

+----+-------+-------+------+
| id | login | score | rank |
+----+-------+-------+------+
| 1  | log1  | 4     | 2    |
+----+-------+-------+------+
| 2  | log2  | 4     | 3    |
+----+-------+-------+------+
| 3  | log3  | 28    | 1    |
+----+-------+-------+------+
| 4  | log4  | 1     | 5    |
+----+-------+-------+------+
| 5  | log5  | 3     | 4    |
+----+-------+-------+------+

Example of output:

+----+-------+-------+------+
| id | login | score | rank |
+----+-------+-------+------+
| 2  | log2  | 4     | 3    |
+----+-------+-------+------+

Best Answer

This is possible with ORDER BY score DESC and user variable.

Create table/insert data

CREATE TABLE logins
    (`id` INT, `login` VARCHAR(4), `score` INT)
;

INSERT INTO logins
    (`id`, `login`, `score`)
VALUES
    (1, 'log1', 4),
    (2, 'log2', 4),
    (3, 'log3', 28),
    (4, 'log4', 1),
    (5, 'log5', 3)
;

Query

With ORDER BY score DESC and user variable this also counts the same score up. 

 SELECT 
   *
   , (@rank := @rank + 1) AS rank
  FROM 
   logins
  CROSS JOIN( 
   SELECT
     @rank := 0
   )  
   AS 
    init_var_var
  ORDER BY
   logins.score DESC

Result

   id  login    score  @rank := 0    rank  
------  ------  ------  ----------  --------
     3  log3        28           0         1
     1  log1         4           0         2
     2  log2         4           0         3
     5  log5         3           0         4
     4  log4         1           0         5

Query

As delivered table so you can filter out one record.

SELECT
   id
 , login
 , score
 , rank
FROM ( 
  SELECT 
   *
   , (@rank := @rank + 1) AS rank
  FROM 
   logins
  CROSS JOIN( 
   SELECT
     @rank := 0
   )  
   AS 
    init_var_var
  ORDER BY
   logins.score DESC  
)
 AS logins_ordered_ranked
WHERE
  id = 2

Result

    id  login    score    rank  
------  ------  ------  --------
     2  log2         4         3

Query without WHERE id=2

SELECT
   id
 , login
 , score
 , rank
FROM ( 
  SELECT 
   *
   , (@rank := @rank + 1) AS rank
  FROM 
   logins
  CROSS JOIN( 
   SELECT
     @rank := 0
   )  
   AS 
    init_var_var
  ORDER BY
   logins.score DESC  
)
 AS logins_ordered_ranked

Result

    id  login    score    rank  
------  ------  ------  --------
     3  log3        28         1
     1  log1         4         2
     2  log2         4         3
     5  log5         3         4
     4  log4         1         5
Related Question