Return row with the max value of one column per group [duplicate]

Question

I am having a hard time doing this without searching the same table at least twice in order to grab the max row, and then grab the value for that row. The table in question is quite big so this is unacceptable.

Here is what my table might look like:

SCORES
ID    ROUND    SCORE
1     1        3
1     2        6
1     3        2
2     1        10
2     2        12
3     1        6

I need to return the score that each ID got in the most recent round. That is, the row with the max (round), but not the max score.

OUTPUT:
ID   ROUND   SCORE
1    3       2
2    2       12
3    1       6

Right now I have:

SELECT * FROM 
(SELECT id, round,
CASE WHEN (MAX(round) OVER (PARTITION BY id)) = round THEN score ELSE NULL END score
 FROM
 SCORES
 where id in (1,2,3)
) scorevals
WHERE
scorevals.round is not null;

This works, but is pretty inefficient (I have to manually filter out all of these rows, when I should just be able to not grab those rows in the first place.)

What can I do to get the right values?

Answer 1

This is also possible without subquery:

SELECT DISTINCT
       id
      ,max(round) OVER (PARTITION BY id) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;

Returns exactly what you asked for.
The crucial point is that DISTINCT is applied after window functions.

SQL Fiddle.

Maybe faster because it uses the same window twice:

SELECT DISTINCT
       id
      ,first_value(round) OVER (PARTITION BY id ORDER BY round DESC) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;

Otherwise doing the same.