How to perform grouped ranking in MySQL

Question

So I have a table as follows:

ID_STUDENT | ID_CLASS | GRADE -----------------------------    1       |    1     |  90    1       |    2     |  80    2       |    1     |  99    3       |    1     |  80    4       |    1     |  70    5       |    2     |  78    6       |    2     |  90    6       |    3     |  50    7       |    3     |  90 

I need to then group, sort and order them to give:

ID_STUDENT | ID_CLASS | GRADE | RANK ------------------------------------     2      |    1     |  99   |  1     1      |    1     |  90   |  2     3      |    1     |  80   |  3     4      |    1     |  70   |  4     6      |    2     |  90   |  1     1      |    2     |  80   |  2     5      |    2     |  78   |  3     7      |    3     |  90   |  1     6      |    3     |  50   |  2 

Now I know that you can use a temp variable to rank, like here, but how do I do it for a grouped set? Thanks for any insight!

Answer 1

SELECT id_student, id_class, grade,    @student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn,    @class:=id_class AS clset FROM   (SELECT @student:= -1) s,   (SELECT @class:= -1) c,   (SELECT *    FROM mytable    ORDER BY id_class, id_student   ) t 

This works in a very plain way:

1. Initial query is ordered by id_class first, id_student second.
2. @student and @class are initialized to -1
3. @class is used to test if the next set is entered. If the previous value of the id_class (which is stored in @class) is not equal to the current value (which is stored in id_class), the @student is zeroed. Otherwise is is incremented.
4. @class is assigned with the new value of id_class, and it will be used in test on step 3 at the next row.