Get the rank of a user in a score table
v.shakya
July 6, 2017
MySql
11,847 Views
Below is Scores Table data and structure
| Id |
Name |
score |
| 1 |
Ram |
90 |
| 2 |
Shyam |
85 |
| 3 |
Dhanshyam |
95 |
| 4 |
Sita |
65 |
| 5 |
Geeta |
98 |
| 6 |
Radha |
45 |
|
SELECT id, name, score, FIND_IN_SET( score, (
|
|
SELECT GROUP_CONCAT( score
|
|
ORDER BY score DESC )
|
|
FROM scores )
|
|
) AS rank
|
|
FROM scores order by rank
|
We get following result from above query :
| Id |
Name |
score |
rank |
| 5 |
Geeta |
98 |
1 |
| 3 |
Dhanshyam |
95 |
2 |
| 1 |
Ram |
90 |
3 |
| 2 |
Shyam |
85 |
4 |
| 4 |
Sita |
65 |
5 |
| 6 |
Radha |
45 |
6 |
|
SELECT COUNT(*)+1 as rank
|
|
FROM
|
|
(SELECT score FROM scores ORDER BY score) AS sc
|
|
WHERE score <
|
|
(SELECT score FROM scores where Name='Ram')
|
|
SELECT id, name, score, 1 + (SELECT count( * )
|
|
FROM scores a WHERE a.score > b.score ) AS rank FROM scores b
|
|
WHERE Name = 'Shyam' ORDER BY rank LIMIT 1
|
2017-07-06
V.Shakya Software Developer & Consultant 
