How to Perform Grouped Ranking of Student Performance in MySQL?

Group ranking in MySQL

Suppose you have a table with student performance data, including student ID, class ID, and grade. Ranking students within each class can provide insight into how well they are learning. This can be achieved by using a combination of variables, subqueries, and comparison operators.

The following query uses the variables @student and @class:

<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) s,
  (SELECT @class:= -1) c,
  (SELECT *
   FROM mytable
   ORDER BY id_class, grade DESC, id_student
  ) t</code>

Inquiry process:

1. Data sorting: Innermost subquery (SELECT * FROM mytable ORDER BY id_class, grade DESC, id_student) Sort by class ID first, then by grade in descending order, and finally by student ID. This ensures that students with higher grades within the same class are ranked higher.

2. Variable initialization: Use subqueries to initialize the variables @student and @class to -1.

3. Assignment Rank: The SELECT statement in the CASE statement checks whether the current class ID is the same as the previous class ID. If they are the same, @student is incremented by 1; otherwise, @student is reset to 0. This establishes rankings within each class.

4. Update @class: The @class variable is assigned the current class ID. It is used in subsequent lines in CASE statements to determine if students belong to the same class.

By using variables and subqueries, this query effectively groups students into their respective classes and assigns rankings accordingly, providing a meaningful way to interpret student learning within each group. Note that this query sorts in descending order of grades, with students with higher grades ranked higher. If you need to sort in ascending order, please change ORDER BY id_class, grade DESC, id_student to ORDER BY id_class, grade ASC, id_student.

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