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!

I wonder if MySQL supports the RANK() window function: http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows — David J., Apr 02 '10 at 19:15

Quassnoi · Accepted Answer · 2009-02-10T16:33:18.923

41

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:

Initial query is ordered by 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.

edited Feb 10 '09 at 16:33

answered Feb 10 '09 at 16:05

Quassnoi

413,100
91
616
614

It gave me an error on "set". I modified it a bit and got it to work. I posted it as an answer below. Any way to optimize it? Also, can you explain how it works? Thanks for the help! – achinda99 Feb 10 '09 at 16:24
3

Is this guaranteed to work as expected? MySQL's [documentation](http://dev.mysql.com/doc/refman/5.0/en//user-variables.html) says: "As a general rule, you should never assign a value to a user variable and read the value within the same statement" – Youval Bronicki Jan 04 '13 at 23:40
@YouvalBronicki: no, it's not. To be on the safe side, you should assign `@student` and `@class` in separate statements and/or wrap everything into a stored procedure. However, not all frameworks support connection persistence and stored procedures. – Quassnoi Jan 05 '13 at 00:38
The trick is to initialize the rank variable in the FROM clause – Julio Marins Oct 23 '14 at 14:05
1

@Quassnoi I am curious about these links that I read on ORDER BY in FROM subquery. Apparently, the rows returns are not necessarily ordered. Will the said problem occur in the query that you have written because I see there is an ORDER BY in the FROM subquery? Links: https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/ http://dba.stackexchange.com/questions/82930/database-implementations-of-order-by-in-a-subquery – Jag Oct 14 '15 at 04:22
@Jack: solutions involving session variables are black magic in MySQL. They might work, but their behavior is not documented, they might break on any update and are not in any way portable even across the installations (that means the same queries might yield different results on say two different replicas). If you want to be on the safe side, use client-side processing. – Quassnoi Oct 14 '15 at 08:54
@quassnoi I asked a question related to this after reading this question and the people that replied seems to concur with what you have said. http://dba.stackexchange.com/questions/117954/is-a-ranking-querys-order-by-in-from-subquery-is-preserved/117956#117956 – Jag Oct 14 '15 at 11:03
3

Shouldn't the ordering be `ORDER BY id_class, grade DESC` – Haleemur Ali May 24 '16 at 13:55

Nicolas Payart · Answer 2 · 2013-04-10T15:15:46.733

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!

Instad of `concat(left(@class:=id_class, 0), 0)` you can simply use `if(@class:=id_class, 1, 1)`. It's still a bit hacky with the assignment in the if-condition , but feels easier to understand. — Axel Heider, Mar 23 '18 at 18:32
Also, I'd write the whole thing as as function then: `@student:=if(@class <> id_class, if(@class:=id_class, 1, 1), @student+1)`. But there is one big downside of the whole thing, it cannot be used in views, as no temp variables are allowed there ans thus the answer with the joins must be used — Axel Heider, Mar 23 '18 at 18:41

score 8 · Answer 3 · edited Oct 08 '12 at 08:20

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

Advantage if this is, that is can be used in a view. Using temp variables in views is no possible in MySQL — Axel Heider, Mar 23 '18 at 18:35

score 5 · Answer 4 · answered Feb 10 '09 at 16:25

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

score 3 · Answer 5 · answered Jul 04 '21 at 15:02

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)

score 2 · Answer 6 · answered Mar 26 '20 at 19:36

2

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.

answered Mar 26 '20 at 19:36

reeslabree

127
13

You'll want to `PARTITION BY id_class ORDER BY grade DESC` not `ASC`. You'll probably also want to `ORDER BY id_class, grade DESC`, not just `ID_CLASS`. – Scratte Mar 26 '20 at 21:03
Note that the question was asked before the RANK function existed in MySQL: if you're stuck on a version below 8, no luck. – Russell Fox Sep 03 '20 at 21:33

score 1 · Answer 7 · answered Feb 10 '09 at 16:40

1

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?

answered Feb 10 '09 at 16:40

achinda99

5,020
4
34
42

Mine one is better, of course :) This one will perform a join with a whole class for each row selected, this is bad for performance. On real data you will hardly notice any difference, though. – Quassnoi Feb 10 '09 at 16:47

score 0 · Answer 8 · answered Oct 25 '22 at 02:53

0

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

answered Oct 25 '22 at 02:53

李亞格

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 27 '22 at 22:26

How to perform grouped ranking in MySQL

8 Answers8

Linked

Related