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!
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:
id_class
first, id_student
second.@student
and @class
are initialized to -1
@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.@class
is assigned with the new value of id_class
, and it will be used in test on step 3 at the next row.