Return row with the max value of one column per group

greatest-n-per-group, max, oracle, sql

Solution

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.

Problem

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?

Original source

Related problems