Home / MySql / Get the rank of a user in a score table

Get the rank of a user in a score table

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 Code
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 Code
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 Code
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

About v.shakya

I am V.Shakya, Freelance PHP Programmer. I like to share my ideas, views and knowledge to all of you who come across my website. I am young, enthusiastic, highly motivated and self disciplined person. I completed my studies in Master of Computer Application and currently giving my technical expertise to one of the Big IT company. I have more than Ten years of experience in vast field of Programming , Designing and Development of websites and various software's.

Check Also

How to get second-highest salary of employees in a table

Method 1) Below query can be used to find the nth maximum value, just replace …

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.