Search code examples
sqlmaxpartitioning

SQL max With Ties

TABLE1
STUDENT SCORE   CLASS
1   1   A
1   1   A
1   9   B
2   5   A
2   10  A
3   6   A
3   4   A
3   9   B
3   8   C
3   8   C

I HAVE TABLE1 above and want to take the MAX(SCORE) for all STUDENT-CLASS COMBINATION, but I only wish for ONE Row when there is duplicates, to get this OUTPUT:

STUDENT CLASS   SCORE
1   A   1
1   B   9
2   A   10
3   A   6
3   B   9
3   C   8

My attempts give DUPLICATES when there is a TIE

SELECT STUDENT, CLASS, MAX(SCORE) OVER PARITION BY (STUDENT, CLASS) AS WANT
FROM TABLE1
Solution

  • You don't need a window aggregate here, just use group by

    SELECT STUDENT, CLASS, MAX(SCORE) SCORE
FROM TABLE1
GROUP BY STUDENT, CLASS
ORDER BY STUDENT, CLASS;

    See Demo Fiddle