How to Perform Grouped Ranking in MySQL Using Subqueries and Variables?

Detailed explanation of MySQL group ranking

Introduction

As a database administrator, you may need to rank items in a grouped dataset. MySQL provides a convenient way to perform grouped ranking using a combination of subqueries and temporary variables.

Problem Scenario

Consider the following form:

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

The goal is to rank students within each class, resulting in the following output:

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

Solution

To perform group ranking in MySQL, you can use the following query:

<code class="language-sql">SELECT id_student, id_class, grade,
   @student:=CASE WHEN @class = id_class THEN @student+1 ELSE 0 END AS rn,
   @class:=id_class AS clset
FROM
  (SELECT @student:= -1, @class := -1) s,
  (SELECT *
   FROM mytable
   ORDER BY id_class, grade DESC
  ) t;</code>

Explanation

This query is step-by-step:

1. The initial query sorts first by id_class and then by grade in descending order.
2. Temporary variables @student and @class are initialized to -1.
3. @class is used to determine whether to enter a new group. If the previous value of @class stored in id_class is different from the current id_class, then @student is reset to 0. Otherwise, it is incremented.
4. @class is assigned the new value of id_class and will be used for the next iteration of the query.
5. Finally, rn is calculated as the ranking within each group. Note that in order to ensure correct ranking, the inner query needs to be sorted by id_class and grade in descending order.

This modified query more clearly shows how to use variables to achieve group ranking, and corrects some logical errors in the original query to ensure the accuracy of ranking results. The key lies in the conditional judgment of the CASE WHEN statement and the correct update of the @student and @class variables.

The above is the detailed content of How to Perform Grouped Ranking in MySQL Using Subqueries and Variables?. For more information, please follow other related articles on the PHP Chinese website!