2023-01-16

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



No comments:

Post a Comment