How to perform grouped ranking in MySQL

Solution 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.

Solution 2:

There is a problem with Quassnoi's solution (marked as best answer).

I have the same problematic (i.e. simulating SQL Window Function in MySQL) and I used to implement Quassnoi's solution, using user-defined variables to store previous row value...

But, maybe after a MySQL upgrade or whatever, my query did not work anymore. This is because the order of evaluation of the fields in SELECT is not guaranteed. @class assignment could be evaluated before @student assignment, even if it is placed after in the SELECT.

This is mentionned in MySQL documentation as follows :

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server.

source : http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

Finally I have used a trick like that to be sure to assign @class AFTER reading it :

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

Using left() function is just used to set @class variable. Then, concatenate the result of left() (equal to NULL) to the expected result is transparent.

Not very elegant but it works!

Solution 3:

SELECT g1.student_id
     , g1.class_id
     , g1.grade
     , COUNT(*) AS rank
  FROM grades   AS g1
  JOIN grades   AS g2
    ON (g2.grade, g2.student_id) >= (g1.grade, g1.student_id)
   AND g1.class_id = g2.class_id
 GROUP BY g1.student_id
        , g1.class_id
        , g1.grade
 ORDER BY g1.class_id
        , rank
 ;

Result:

+------------+----------+-------+------+
| student_id | class_id | 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 |
+------------+----------+-------+------+

Solution 4:

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