SQL Take Max and Include Non-Group By COLS-CodePudding

TABLE1
ID  STUDENT SCORE   TIME
A   1   9   1
A   1   8   2
B   1   0   1
B   1   10  2
B   1   7   3
C   2   5   1
C   2   1   2
C   2   0   3
D   3   1   1
E   3   0   1
D   3   4   2
D   3   4   3
E   3   9   2
F   4   6   1
G   4   6   1   

            
            
            
            
            
            
WANT
ID  STUDENT MAXSCORE    TIME
A   1   9   1
B   1   10  2
B   1   7   3
C   2   5   1
C   2   1   2
C   2   0   3
D   3   1   1
E   3   9   2
D   3   4   3
F   4   6   1

I have TABLE1 and wish for WANT which does this: for every STUDENT/TIME, select the row with the MAX(SCORE)

I try this::

select ID, STUDENT, MAX(SCORE) AS MAXSCORE, TIME
from TABLE1
group by STUDENT, TIME

But amn't able to include ID

CodePudding user response：

First get the max score by student/time, then join back to the original table.

WITH dat
AS
(
SELECT student, time, MAX(score) AS max_score
FROM TABLE1
GROUP BY student, time
)
SELECT DISTINCT t.id, t.student, d.max_score, t.time
FROM TABLE1 t
INNER JOIN dat d 
ON t.student = d.student AND t.time = d.time AND t.score = d.max_score;

CodePudding user response：

If the RDBMS supports window functions, then

with cte as (
select id,
       student,
       score,
       time,
       row_number() over (partition by student, time order by score desc) as rn
  from table1)
select id, student, score, time
  from cte
 where rn = 1;