Get the rank of a user in a score table
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.
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 !!!
SELECT
id,
Name,
1+(SELECT count(*) from table_name a WHERE a.Score > b.Score) as RNK,
Score
FROM table_name b;