Get the rank of a user in a score table
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
Idand want to get his rank, where all rows are ordinal ordered descending for the
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.
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
scorecolumn would help performance on large tables.
The correlated `(SELECT GROUP_CONCAT(score) FROM TheWholeTable)` is not the best way. And it may have a problem with the size of the row created.
The single person score query is extremely slow for larger tables.. A much better query to determine rank (with gaps for ties) for a single person's score is: `SELECT 1 + COUNT(*) AS rank FROM scores WHERE score > (SELECT score FROM scores WHERE name='Assem')`. Which 'just' counts the number of entries with a higher score than the current entry. (If you add `DISTINCT` you will get the rank without gaps..)
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:[email protected]+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:[email protected]+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 !!!
Since this is using session-scoped variables, is this safe if, say, multiple end-users are requesting the scoreboard at the same time? Is it possible for the result set to have different results because another user is also executing this query? Imagine an API in front of this query with many clients hitting it at once.
@XaeroDegreaz You are right, It is possible. Imagine calculating ranks for a game. One user queries for rank and another user queries 5 seconds after a person beats the high score or enters the top X scores. Notwithstanding, the same can happen if the ranking were done at the application level rather than the server level.
Thanks for the reply. My concern isn't really whether or not the data shifts organically over time, my concern is that multiple users performing the query would be modifying / overwriting the data stored in the session-scoped variables while other users are also performing the query. Does that make sense?
@XaeroDegreaz that's the beauty of session scope variables. They are in your session only, and nobody else's. You will not see session variables from other users and nobody will see your session variables.
Okay, that's what I was sort of leaning towards believing -- that session variables are scoped to the connection, and a single-connection cannot be occupied by more than one person at a time. Once the connection is free, or thrown back into the pool, another user can hop on the connection and the session variables are re-initialized (when performing this query). Thanks again for the information.
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
GROUP_CONCATto 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
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'
id name score rank 2 Boo 58 4
The single user's rank query can be optimized enormously by using `COUNT` and a subquery instead. See my comment at the Accepted Answer
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:
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.