Calculate rank of the users based on their max score using mysql

Question

I have a table (called users) I need rank of users based on their score but I want rank on the bases of users max score.

+-----------+------------+
| User_id   | Score      |
+-----------+------------+
| 1         | 12258      | 
| 1         | 112        |
| 2         | 9678       |
| 5         | 9678       |
| 3         | 689206     |
| 3         | 1868       |

Expect result

+-----------+------------+---------+
| User_id   | Score      | Rank    |
+-----------+------------+---------+
| 3         | 689206     |   1     |     
| 1         | 12258      |   2     |
| 2         | 9678       |   3     |
| 5         | 9678       |   3     |

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Oct 27 '18 at 09:36

D-Shih · Accepted Answer · 2018-10-27T09:50:42.933

You are looking for DENSE_RANK, But it supports mysql version higher than 8.0

use correlated-subquery to get max value by each User_id
use two variables one to store rank another to store previous value to make the DENSE_RANK number.

look like this.

CREATE TABLE T(
   User_id int,
   Score int
); 

insert into t values (1,12258); 
insert into t values (1,112);
insert into t values (2,9678);
insert into t values (5,9678);
insert into t values (3,689206);
insert into t values (3,1868);

Query 1:

SELECT User_id,Score,Rank
FROM (
  SELECT User_id,
         Score,
         @rank :=IF(@previous = t1.score, @rank, @rank + 1) Rank,
         @previous := t1.Score
  FROM T t1 CROSS JOIN (SELECT @Rank := 0,@previous := 0) r
  WHERE t1.Score = 
  (
    SELECT  MAX(Score)  
    FROM T tt
    WHERE t1.User_id = tt.User_id
  ) 
  ORDER BY Score desc
) t1

Results:

| User_id |  Score | Rank |
|---------|--------|------|
|       3 | 689206 |    1 |
|       1 |  12258 |    2 |
|       2 |   9678 |    3 |
|       5 |   9678 |    3 |

Please View my Expect result – Gaurav Kandpal Oct 27 '18 at 09:39 — Gaurav Kandpal, Oct 27 '18 at 09:39

LukStorms · Answer 2 · 2020-01-22T08:06:46.507

2

Another trick in MySql 5.7 to calculate a DENSE_RANK (like in MySql 8) is to use a CASE WHEN with the variable assignments in it.

SELECT User_id, MaxScore AS Score,
  CASE 
  WHEN MaxScore = @prevScore THEN @rnk
  WHEN @prevScore := MaxScore THEN @rnk := @rnk+1 
  ELSE @rnk := @rnk+1
  END AS Rank
FROM 
(
  SELECT User_id, MAX(Score) AS MaxScore
  FROM YourTable
  GROUP BY User_id
  ORDER BY MaxScore DESC, User_id
) AS q
CROSS JOIN (SELECT @rnk := 0, @prevScore := null) AS vars

You can test it here on rextester.

edited Jan 22 '20 at 08:06

answered Oct 27 '18 at 10:32

LukStorms

28,916
5
31
45

This is the correct answer. MySQL does not guarantee the order of evaluation of expressions in a `SELECT`, so a variable should not be assigned in one expression and referenced in another. – Gordon Linoff Oct 27 '18 at 11:23
But about those that have score 0 ? – Mecanik Jan 22 '20 at 06:29
@NorbertBoros Well spotted. A 0 is false in MySql, so the second `WHEN` didn't add 1. F.e. `select case when 0 then true else false end` returns 0. Adding an `ELSE` fixed it. – LukStorms Jan 22 '20 at 10:03

Calculate rank of the users based on their max score using mysql

2 Answers2

Linked