How to Rank Data Within Groups in MySQL?

Detailed explanation of MySQL group ranking

In some scenarios, data needs to be ranked based on groupings rather than relying solely on a single field. For example, suppose there is a student table containing student ID, class ID and grades 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

To rank students in each class based on their grades, the following MySQL query can be used:

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

This query contains the following steps:

1. Variable initialization: The two user variables @student and @class are initialized to 0.

2. Row-by-row processing: The outer query processes each row of the result set in the order specified by the ORDER BY clause (id_class, grade DESC, id_student). Note that grade DESC is used in sorting here to ensure that students with high scores are ranked first.

3. In-group ranking: The CASE statement checks whether the current id_class is the same as the previous id_class (@class). If they are the same, add 1 to @student; otherwise, reset @student to 1. This ensures that the rn value (rank) is unique within each group (id_class).

4. Grouped assignment: @class is assigned the current id_class and will be compared on the next line.

The output of this query will provide the desired student grouping rankings:

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

The above is the detailed content of How to Rank Data Within Groups in MySQL?. For more information, please follow other related articles on the PHP Chinese website!