SQL MAX(col1, col2) with priorities-CodePudding

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), 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.

CodePudding user response：

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.value1 = (select max(n2.value1) from nums n2 where n2.value2 = n.value2);

Or a window function:

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

CodePudding user response：

First aggregate to get max values of both num1 and num2 for each name and also a flag indicating which of the 2 should be preferred and then join to the table:

SELECT n.*
FROM nums n 
INNER JOIN (
  SELECT name, MAX(num1) max_num1, MAX(num2) max_num2,
         COUNT(DISTINCT num1) = COUNT(*) flag
  FROM nums
  GROUP BY name
) t ON t.name = n.name 
   AND (CASE WHEN flag THEN n.num1 = t.max_num1 ELSE n.num2 = t.max_num2 END);

See the demo.