Order students by highest grade and then order grades for each student

user_4329077 0
Viewed 131
Student Grade Course
JOHN 10 Algebra
JOHN 7 Computer science
ANNA 10 Algebra
ANNA 10 Data structures
JACOB 10 Data structures
GEORGE 6 Computer science
GEORGE 10 Algebra
GEORGE 9 Data structures

I have the above table and what I want to do is to order the students by their highest course grade (a student with 10 & 10 comes first compared to someone with a 10 or 10 & 9 or even someone with 10 & 9 & 9) and then order the courses by grade for each student. The result should look like the table below:

Student Grade Course
ANNA 10 Data structures
ANNA 10 Computer science
GEORGE 10 Algebra
GEORGE 9 Data structures
GEORGE 6 Algebra
JOHN 10 Algebra
JOHN 7 Computer science
JACOB 10 Data structures

Is there a way to achieve this result?

sql-order-by mysql sql
2 Answers

You can use window functions in the ORDER BY clause. So:

order by max(grade) over (partition by student) desc,
         student,
         grade desc
user_11440350

With this query:

SELECT Student, GROUP_CONCAT(LPAD(Grade, 2, '0') ORDER BY Grade DESC) grades
FROM tablename
GROUP BY Student

you get for each student all grades left padded with 0 in a comma separated list:

Student grades
ANNA 10,10
GEORGE 10,09,06
JACOB 10
JOHN 10,07

You can join the above query to the table and sort by that list of grades descending:

SELECT t.*
FROM tablename t 
INNER JOIN (
  SELECT Student, GROUP_CONCAT(LPAD(Grade, 2, '0') ORDER BY Grade DESC) grades
  FROM tablename
  GROUP BY Student
) g ON g.Student = t.Student
ORDER BY grades DESC, Grade DESC

See the demo.
Results:

Student Grade Course
ANNA 10 Algebra
ANNA 10 Data structures
GEORGE 10 Algebra
GEORGE 9 Data structures
GEORGE 6 Computer science
JOHN 10 Algebra
JOHN 7 Computer science
JACOB 10 Data structures
user_104988280
Related