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, (
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.



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

SELECT s.id, 
       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`
| improve this answer | |
  • Keep in mind that GROUP_CONCAT has a default limit of 1024 characters. (This can be changed.) – Rick James Dec 17 '19 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.