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.