Return row with the max value of one column per group

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.

Answer 2

You're on the right track using analytic functions. But you probably want something like this with the rank function

SELECT *
  FROM (SELECT a.*,
               rank() over (partition by id order by round desc) rnk
          FROM scores
         WHERE id IN (1,2,3))
 WHERE rnk = 1

If there can be ties (rows that have the same id and round) you may want to use the row_number analytic function instead of rank-- that will arbitrarily pick one of the two tied rows to have a rnk of 1 rather than returning both as rank would.

If you wanted to use the MAX analytic function, you could also do something like

SELECT *
  FROM (SELECT a.*,
               MAX(round) OVER (partition by id) max_round
          FROM scores
         WHERE id IN (1,2,3))
 WHERE round = max_round

Answer 3

For this kind of problems, I tend to use the max...keep...dense_rank construct:

select
  id,
  max(round)  round,
  max(score) keep (dense_rank last order by round) score
from
  tq84_scores
group by
  id;

sql fiddle