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

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 scoreFROM   SCORESWHERE  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 scoreFROM   SCORESWHERE  id IN (1,2,3)ORDER  BY id;

Otherwise doing the same.

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

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) scorefrom  tq84_scoresgroup by  id;

sql fiddle