SQL : Keep ONE row with max value on a column depending on value of another column

It’s well documented on StackOverflow how to find the whole data for the row with some max value in a column per some group identifier (SQL select only rows with max value on a column).

But that given solution would display all rows with that max value. What if we have 3 cols “ID, col1, col2” and we want to keep, for each ID, the row with the highest value of col1 BUT if there are more than one, only keep the instance with the lowest value of col2 ?

Thanks !

Answer

One method is rank() or row_number();

select t.*
from (select t.*,
             row_number() over (partition by id order by col1 desc, col2 asc) as seqnum
      from t
     ) t
where seqnum = 1;

You would use rank() if you want multiple rows when there are duplicate max col1/ min col2 for the same id.