How to perform grouped ranking in MySQL

by 
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.

More Related Contents:

  1. MySql using correct syntax for the over clause
  2. SQL Query needed for fetch value from Two identical table but not fully identical table
  3. SQL select only rows with max value on a column [duplicate]
  4. How to create a MySQL hierarchical recursive query?
  5. How to reset AUTO_INCREMENT in MySQL
  6. MySQL Query GROUP BY day / month / year
  7. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  8. How to select a column name with a space in MySQL [duplicate]
  9. Can a table field contain a hyphen?
  10. How do I use on delete cascade in mysql?
  11. Adding a leading zero to some values in column in MySQL
  12. Using SQL to determine word count stats of a text field
  13. Update one MySQL table with values from another
  14. MySQL JOIN the most recent row only?
  15. How to transpose mysql table rows into columns
  16. MySQL Group By and Sum total value of other column
  17. Search all columns of a table using a single where condition with single keyword in mysql
  18. What does SQL clause “GROUP BY 1” mean?
  19. Syntax error when using except in a query
  20. MySQL query, MAX() + GROUP BY
  21. Why the order of evaluation for expressions involving user variables is undefined?
  22. ALTER table – adding AUTOINCREMENT in MySQL
  23. sql like operator on integer
  24. MySQL – SELECT * INTO OUTFILE LOCAL ?
  25. The best way to remove value from SET field?
  26. INSERT INTO Table from multiple tables
  27. Execute INSERT if table is empty?
  28. Mysql How do you create a clustered index?
  29. MySQL: Computed Column
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment