Mysql – Ranking unique rows in a table – MySQL

MySQL

I am trying to rank the table below based on score and class, I have been looking for a way to isolate each class so they can have their unique rank.

This is the main table

Id  Name   Class     Score
1   Stu1   Grade 1   100
2   Stu2   Grade 1   50
3   Stu3   Grade 2   10
4   Stu4   Grade 3   10
5   Stu5   Grade 3   88
6   Stu6   Grade 1   99

For instance, ranking students in Grade 1 should be something like

Name    Rank
Stu1    1
Stu6    2
Stu2    3

Then if i wanna rank Grade 3 Students, it should result to;

Name    Rank
Stu5    1
Stu4    2

I used this code 

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( DISTINCT score
ORDER BY score DESC ) FROM scores)
) AS rank
FROM scores WHERE class = 'Grade 1'

but it keeps ranking the entire column which isn't what I want

Kindly help me…I'm really stuck on this.

Thanks

Best Answer

While keeping your approach to the solution, the query will be

SELECT s.id, 
       s.class,
       s.name, 
       s.score, 
       FIND_IN_SET( s.score, ( SELECT GROUP_CONCAT( DISTINCT ss.score
                                                    ORDER BY ss.score DESC ) 
                               FROM scores ss
                               WHERE s.class = ss.class ) ) AS `rank`
FROM scores s
-- WHERE class = 'Grade 1'
ORDER BY class, `rank`
Related Question