2

Say you have a table with studentID, class, grade. I want the maximum grade for each class. This is easy, just group by class and get max(grade). But the problem I'm having is how to also get the studentID.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Umar Farooq
  • 73
  • 2
  • 6
  • Check this out: http://stackoverflow.com/questions/41049947/how-do-i-select-the-max-score-from-each-distinct-user-in-this-table – mechanical_meat Dec 09 '16 at 20:57

2 Answers2

2

Instead of using an aggregate function, you could use window functions:

SELECT class, grade, studentId
FROM   (SELECT class, grade, studentId, 
               RANK() OVER (PARTITION BY class ORDER BY grade DESC) rk
        FROM   students)
WHERE  rk = 1
Mureinik
  • 297,002
  • 52
  • 306
  • 350
1

I think distinct on is a good way to go:

select distinct on (s.class) s.*
from students
order by s.class, s.grade desc;

However you probably want all students for each class with the maximum grade. If so, Mureinik's solution is better.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786