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.

Answer 2

Modified from above, this works but its more complex than I think it needs to be:

SELECT ID_STUDENT, ID_CLASS, GRADE, RANK
FROM
    (SELECT ID_STUDENT, ID_CLASS, GRADE,
     @student:=CASE WHEN @class <> id_class THEN 1 ELSE @student+1 END AS RANK,
     @class:=id_class AS CLASS
    FROM
     (SELECT @student:= 0) AS s,
     (SELECT @class:= 0) AS c,
     (SELECT * 
      FROM Students
      ORDER BY ID_CLASS, GRADE DESC
     ) AS temp
    ) AS temp2

Answer 3

I did some searching, found this article to come up with this solution:

SELECT S2.*, 
FIND_IN_SET(
S2.GRADE
, (
SELECT GROUP_CONCAT(GRADE ORDER BY GRADE DESC)
FROM Students S1
WHERE S1.ID_CLASS = S2.ID_CLASS
)
) AS RANK
FROM Students S2 ORDER BY ID_CLASS, GRADE DESC;

Any thoughts on which is better?