SQL query to pick the IDs according to below details-CodePudding

I need to determine the last event in the data when condition1 is 13 and condition2 is 14. However, it should not pick those IDs which have already passed through condition1 as 15 and condition2 as 16 and then again the last event is 13 and 14. i.e, in the below data, it should not pick the ids 102 and 103.

The data is as follows

id   datetime       date           condition1   condition2
101 01-08-2021 13:00:41 01-08-2021    11          12    
101 06-08-2021 08:08:21 05-08-2021    13          14    
101 07-08-2021 21:05:32 07-08-2021    15          16
102 05-08-2021 14:08:32 05-08-2021    11          12
102 08-08-2021 06:13:13 08-08-2021    13          14
102 10-08-2021 13:09:55 10-08-2021    15          16
102 11-08-2021 18:00:00 11-08-2021    13          14
103 26-08-2021 14:04:22 26-08-2021    11          12    
103 28-08-2021 12:09:08 28-08-2021    13          14
103 31-08-2021 17:45:00 31-08-2021    15          16
103 02-09-2021 07:00:04 02-09-2021    17          18
103 05-09-2021 09:00:04 05-09-2021    13          14
104 21-08-2021 11:11:12 21-08-2021    11          12    
104 25-08-2021 10:09:35 25-08-2021    13          14
104 31-08-2021 08:35:40 31-08-2021    15          16
105 23-08-2021 09:05:54 23-08-2021    11          12
105 24-08-2021 10:00:22 24-08-2021    13          14

Expected output

id      datetime           date       condition1   condition2
105 04-09-2021 10:00:22 24-08-2021        13          14

CodePudding user response：

I would use a subquery to exclude ids that have condition1 = 15 and condition2 = 16

SELECT * FROM mytable
WHERE 
    condition1 = 13 
    AND condition2 = 14
    AND id NOT IN (
        SELECT id FROM mytable WHERE condition1 = 15 AND condition2 = 16
    )
ORDER BY datetime DESC
LIMIT 1;

CodePudding user response：

select *
from vt
qualify row_number()     -- last event in the data 
        over (partition by id
              order by datetime desc) = 1
    and condition1 = 13  -- when condition1 is 13 
    and condition2 = 14  -- and condition2 is 14

    -- not pick those IDs which have already passed through condition1 as 15 and condition2 as 16
    and count(case when condition1 = 15 and condition2 = 16 then 1 end)
        over (partition by id) = 0
;