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

How about rank() over(partition by class_id order by grade desc) ? https://www.mysqltutorial.org/mysql-window-functions/mysql-rank-function/

Answer 2

While I don't have enough reputation points to comment (a little humorous), MySQL has come a long way in recent years. Window functions and CTE (WITH clause) have been added, which means rank (and row_number, etc) is now supported.

I'm the same "Jon Armstrong - Xgc", but that account was lost to the winds of old email addresses.

A comment raised a question about whether MySQL supported the rank window function. Answer: Yes.

My original response, a few years ago:

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

Results:

+------------+----------+-------+------+
| 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 |
+------------+----------+-------+------+
9 rows in set (0.001 sec)

Using ROW_NUMBER window function:

WITH cte1 AS (
        SELECT student_id
             , class_id
             , grade
             , ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY grade DESC) AS rank
          FROM grades
     )
SELECT *
  FROM cte1
 ORDER BY class_id, r
;

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 |
+------------+----------+-------+------+
9 rows in set (0.002 sec)

Using RANK window function:

WITH cte1 AS (
        SELECT student_id
             , class_id
             , grade
             , RANK() OVER (PARTITION BY class_id ORDER BY grade DESC) AS rank
          FROM grades
     )
SELECT *
  FROM cte1
 ORDER BY 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 |
+------------+----------+-------+------+
9 rows in set (0.000 sec)

Answer 3

SELECT ID_STUDENT, ID_CLASS, GRADE, RANK() OVER(
PARTITION BY ID_CLASS
ORDER BY GRADE ASC) AS 'Rank'
FROM table
ORDER BY ID_CLASS;

I had a similar problem for a homework assignment, found that MySQL (can't speak for any other RDBMS) has a partition argument for its RANK() method. Don't see why it wouldn't work for this problem.

Answer 4

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!

Answer 5

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

Answer 6

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?

Answer 7

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 8

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