0

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 !

mlx
  • 504
  • 1
  • 4
  • 15
  • Also well-documented on SO: [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jan 25 '21 at 23:25
  • Just add an extra column to the `order by` – Charlieface Jan 25 '21 at 23:34

1 Answers1

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786