SQL MAX(col1, col2) with priorities

I need to select row with MAX(val1) for each group. If there is more than one row with equal val1 = MAX(val1) I need to select from them row with MAX(val2), etc. In pseudo code it should be like this:

IF row1.val1 != row2.val1
    GET MAX(val1)
ELSE
    GET MAX(val2)

For example:

I have table nums with one TEXT column name and three INTEGER columns — id, num1 and num2 filled with values:

id name num1 num2
1 Name1 10 20
2 Name1 10 30
3 Name2 08 15
4 Name2 10 20
5 Name3 20 2
6 Name3 20 1

I need a query which will return me this:

2
4
5

It could be something like

SELECT id FROM nums GROUP BY name HAVING MAX(num1, num2)

but MAX(num1, num2) doesn’t work.

I’m using sqlite3 module with python3.8.

Answer

I need to select row with MAX(val1) for each group. If there is more than one row with equal val1 I need to select from them row with MAX(val2)

You can use a correlated subquery:

select n.*
from nums n
where n.num2 = (select max(n2.num2) from nums n2 where n2.num1 = n.num1);

Or a window function:

select n.*
from (select n.*,
             row_number() over (partition by num1 order by num2 desc) as seqnum
      from nums n
     ) n
where seqnum = 1;