Get the rank of a user in a score table

Question

I have a very simple MySQL table where I save highscores. It looks like that:

Id     Name     Score

So far so good. The question is: How do I get what's a users rank? For example, I have a users Name or Id and want to get his rank, where all rows are ordinal ordered descending for the Score.

An Example

Id  Name    Score
1   Ida     100
2   Boo     58
3   Lala    88
4   Bash    102
5   Assem   99

In this very case, Assem's rank would be 3, because he got the 3rd highest score.

The query should return one row, which contains (only) the required Rank.

score 44 · Accepted Answer · edited Jul 20 '17 at 21:43

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.

score 35 · Answer 2 · edited Oct 03 '17 at 20:00

When multiple entries have the same score, the next rank should not be consecutive. The next rank should be incremented by number of scores that share the same rank.

To display scores like that requires two rank variables

rank variable to display
rank variable to calculate

Here is a more stable version of ranking with ties:

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,ID,rank FROM
(
    SELECT AA.*,BB.ID,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (SELECT COUNT(1) scorecount,score
        FROM scores GROUP BY score
    ) AAA
    ORDER BY score DESC
) AA LEFT JOIN scores BB USING (score)) A;

Let's try this out with sample data. First Here is the sample data:

use test
DROP TABLE IF EXISTS scores;
CREATE TABLE scores
(
    id int not null auto_increment,
    score int not null,
    primary key (id),
    key score (score)
);
INSERT INTO scores (score) VALUES
(50),(40),(75),(80),(55),
(40),(30),(80),(70),(45),
(40),(30),(65),(70),(45),
(55),(45),(83),(85),(60);

Let's load the sample data

mysql> DROP TABLE IF EXISTS scores;
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE TABLE scores
    -> (
    ->     id int not null auto_increment,
    ->     score int not null,
    ->     primary key (id),
    ->     key score (score)
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO scores (score) VALUES
    -> (50),(40),(75),(80),(55),
    -> (40),(30),(80),(70),(45),
    -> (40),(30),(65),(70),(45),
    -> (55),(45),(83),(85),(60);
Query OK, 20 rows affected (0.04 sec)
Records: 20  Duplicates: 0  Warnings: 0

Next, let initialize the user variables:

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Now, here is the output of the query:

mysql> SELECT score,ID,rank FROM
    -> (
    ->     SELECT AA.*,BB.ID,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    ->     (@curscore:=score) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (SELECT COUNT(1) scorecount,score
    ->         FROM scores GROUP BY score
    ->     ) AAA
    ->     ORDER BY score DESC
    -> ) AA LEFT JOIN scores BB USING (score)) A;
+-------+------+------+
| score | ID   | rank |
+-------+------+------+
|    85 |   19 |    1 |
|    83 |   18 |    2 |
|    80 |    4 |    3 |
|    80 |    8 |    3 |
|    75 |    3 |    5 |
|    70 |    9 |    6 |
|    70 |   14 |    6 |
|    65 |   13 |    8 |
|    60 |   20 |    9 |
|    55 |    5 |   10 |
|    55 |   16 |   10 |
|    50 |    1 |   12 |
|    45 |   10 |   13 |
|    45 |   15 |   13 |
|    45 |   17 |   13 |
|    40 |    2 |   16 |
|    40 |    6 |   16 |
|    40 |   11 |   16 |
|    30 |    7 |   19 |
|    30 |   12 |   19 |
+-------+------+------+
20 rows in set (0.18 sec)

Please note how multiple IDs that share the same score have the same rank. Also note that rank is not consecutive.

Give it a Try !!!

score 17 · Answer 3 · edited Jul 20 '17 at 21:45

17

SELECT 
    id, 
    Name,
    1+(SELECT count(*) from table_name a WHERE a.Score > b.Score) as RNK,
    Score
FROM table_name b;

edited Jul 20 '17 at 21:45

Paul White

94,921
30
437
687

answered Feb 22 '12 at 21:00

a1ex07

9,060
3
27
41

score 9 · Answer 4 · answered Feb 22 '12 at 21:07

9

One option would be to use USER variables:

SET @i=0;
SELECT id, name, score, @i:=@i+1 AS rank 
 FROM ranking 
 ORDER BY score DESC;

answered Feb 22 '12 at 21:07

Derek Downey

23,568
11
79
104

score 4 · Answer 5 · edited Jul 20 '17 at 19:55

The accepted answer has a potential problem. If there are two or more identical scores, there will be gaps in the ranking. In this modified example:

 id name  score rank
 1  Ida   100   2
 2  Boo    58   5
 3  Lala   99   3
 4  Bash  102   1
 5  Assem  99   3

The score of 58 has rank 5, and there is no rank 4.

If you want to make sure there are no gaps in the rankings, use DISTINCT in the GROUP_CONCAT to build a list of distinct scores:

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( DISTINCT score
ORDER BY score DESC ) FROM scores)
) AS rank
FROM scores

Result:

id name  score rank
1  Ida   100   2
2  Boo    58   4
3  Lala   99   3   
4  Bash  102   1
5  Assem  99   3

This also works for getting a single user's rank:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT(DISTINCT score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Boo'

Result:

id name score rank
 2  Boo   58    4

score 3 · Answer 6 · answered Mar 02 '13 at 17:00

3

Here's the best answer:

SELECT 1 + (SELECT count( * ) FROM highscores a WHERE a.score > b.score ) AS rank FROM
highscores b WHERE Name = 'Assem' ORDER BY rank LIMIT 1 ;

This query will return:

3

answered Mar 02 '13 at 17:00

FamerJoe

39
1

score 3 · Answer 7 · edited Jul 20 '17 at 21:47

3

This solution gives the DENSE_RANK in case of ties:

SELECT *,
IF (@score=s.Score, @rank:=@rank, @rank:=@rank+1) rank,
@score:=s.Score score
FROM scores s,
(SELECT @score:=0, @rank:=0) r
ORDER BY points DESC

edited Jul 20 '17 at 21:47

Paul White

94,921
30
437
687

answered May 03 '13 at 11:16

Arvind07

131
3

score 0 · Answer 8 · answered Nov 02 '19 at 12:37

0

Wouldn't the following work (assuming your table is called Scores)?

SELECT COUNT(id) AS rank FROM Scores 
WHERE score <= (SELECT score FROM Scores WHERE Name = "Assem")

answered Nov 02 '19 at 12:37

bfredo123

113
5

Hieu Vo · Answer 9 · 2021-07-24T07:21:13.850

0

Improved version of @a1ex07, to consider duplicated scores

SELECT 
    id, 
    Name,
    (SELECT count(*) 
        from (select Score from table_name group by Score) a 
        WHERE a.Score > b.Score) as RNK,
    Score
FROM table_name b;

You can replace the nested group select by count(DISTINCT Score) instead of count(*) to simplify the query, but the performance will be dramatically decreased

edited Jul 24 '21 at 07:21

answered Jul 24 '21 at 07:13

Hieu Vo

101
3

score -4 · Answer 10 · answered Jul 09 '13 at 12:13

I have this, which gives the same results as the one with variables. It works with ties and it may be faster:

SELECT COUNT(*)+1 as rank
FROM 
(SELECT score FROM scores ORDER BY score) AS sc
WHERE score <
(SELECT score FROM scores WHERE Name="Assem")

I didn't test it, but I'm using one that works perfect, which I adapted to this with the variables you were using here.

Get the rank of a user in a score table

10 Answers10

Linked

Related