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 ?
One method is
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.