CODEWITHSUNDEEP
mysqlsqlsql-order-by
Babis.amas
Babis.amas

Reputation: 471

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

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?

Upvotes: 1

Views: 361

Answers (2)

forpas
forpas

Reputation: 164089

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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

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

Upvotes: 3

Related Questions