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.

Please fill in the form and submit to subscribe

Welcome to VShakya

For this week my all price is very low !

Don't miss to get my services

Thanks

 

Contact us