Return row with maximum value of one column per group

Question

It’s not easy for me to do this unless you search for the same table at least twice to grab the maximum row, and then grab the value for that row. This table is quite large, so this is unacceptable.

This is what my table looks 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 identifier received in the last round. That is, a line with max (round), but not a maximum score.

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

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 all these lines, when I just have to just not capture these lines in the first place.)

What can I do to get the correct values?

Answer 1

This is also possible without a 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 requested.
The decisive 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, do the same.

Answer 2

You are 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 links (rows that have the same id and round ), you can use the analytical function row_number instead of rank -, which will arbitrarily select one of the two related rows to have rnk of 1, and not return both values ​​as rank .

If you want to use the analytic function MAX , you can 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 such problems, I tend to use the max...keep...dense_rank :

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

sql script