Write a (better)query that returns an aggregate with an associated value?
Say I have table with 3 columns:
col1 col2 col3
1 green 10
1 blue 15
3 red 20
3 yellow 5
4 purple 17
4 black 11
I would like to obtain the max of col3, grouped by col1, while having the value of col2 that belongs to the same row as max(col3):
In other words, the following result set is the desired result:
col1 col2 col3
1 blue 15
3 red 20
4 purple 17
One way I thought of doing this is:
SELECT col1, col3, col2
FROM
(
SELECT ROW() OVER (PARITTION BY col1 ORDER BY col3 DESC) AS rn, col1, col3, col2
GROUP BY col1
) sub
WHERE sub.rn = 1;
But it feels like overkill(if it works), and I'd like to know if there is a much better, more efficient way?
Thanks
Comments
Post a Comment