SQL compares the value of 2 columns and select the column with max value row-by-row

I have table something like:

GROUP	NAME	Value_1	Value_2
1	ABC	0	0
1	DEF	4	4
50	XYZ	6	6
50	QWE	6	7
100	XYZ	26	2
100	QWE	26	2

What I would like to do is to groupby group and select the name with highest value_1. If their value_1 are the same, compare and select the max with value_2. If they’re still the same, select the first one. The output will be something like:

GROUP	NAME	Value_1	Value_2
1	DEF	4	4
50	QWE	6	7
100	XYZ	26	2

The challenge for me here is I don’t know how many categories in NAME so a simple case when is not working. Thanks for help

Answer

You can use window functions to solve the bulk of your problem:

select t.*
from (select t.*,
             row_number() over (partition by group order by value1 desc, value2 desc) as seqnum
      from t
     ) t
where seqnum = 1;

The one caveat is the condition:

If they’re still the same, select the first one.

SQL tables represent unordered (multi-) sets. There is no “first” one unless a column specifies the ordering. The best you can do is choose an arbitrary value when all the other values are the same.

That said, you might have another column that has an ordering. If so, add that as a third key to the order by.