TABLE1

Required table

I want to get the max optout value other than null if there are any other values for a mach_id based on time. If every optout value is null for mach_id optout should be null. How can I query it?

I wrote this query but it's not working

  Select max(COALESCE(OPTOUT, 0)) AS optout,mach_id
from
(select OPTOUT,mach_id, time from table1)
where 
time=max(time)
group by mach_id

CodePudding user response：

It is possible to achieve it using QUALIFY:

SELECT *
FROM tab
QUALIFY ROW_NUMBER() OVER(PARTITION BY mach_id 
                          ORDER BY optout IS NOT NULL DESC, time DESC) = 1;

Output:

CodePudding user response：

We can use ROW_NUMBER here:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY mach_id
                                   ORDER BY optout IS NULL, time DESC) rn
    FROM table1 t
)

SELECT mach_id, optout
FROM cte
WHERE rn = 1;

The sort in the call to ROW_NUMBER places, for each group of mach_id records, the non null optout values first. Within those subgroups, the record with the latest time is chosen. A null optout record can only appear in the result set if only such null records are available.