SQL query to pick the last entry with a condition-CodePudding

I am trying to query the data where I need to pick those data whose last event is 3 in condition1 and 4 in condition2 and the last event should be within the month of January.

The data is as below,

col1    condition1  condition2  date
1234    0              1        01/01/2020  
1234    1              2        02/01/2020  
1234    2              3        04/01/2020  
1234    3              4        10/01/2020  
5678    0              1        25/01/2020  
5678    1              2        26/01/2020  
5678    2              3        28/01/2020  
5678    3              4        03/02/2020  
8901    0              1        17/01/2020  
8901    1              2        18/01/2020  
8901    2              3        20/01/2020  
8901    3              4        22/01/2020  
8901    4              5        24/01/2020  
8901    5              6        26/01/2020  
3467    0              1        13/01/2020  
3467    1              2        15/01/2020  
3467    2              3        16/01/2020  
3467    3              4        24/01/2020  
3467    4              5        27/01/2020  
3467    5              6        28/01/2020

Expected Output

col1    condition1  condition2  date
1234    3              4        10/01/2020

CodePudding user response：

select *
from mytable
qualify
   rank()
   over (partition by col1
         order by datecol desc) = 1 -- last event
-- additional conditions
and datecol between date '2021-01-01' and date '2021-01-31'
and condition1 = 3
and condition2 = 4;

CodePudding user response：

Translate "...pick the last entry with a condition [...] whose last event is 3 in condition1 and 4 in condition2 and the last event should be within the month of January." to SQL:

SELECT col1, condition1, condition2, "date"
FROM tablename AS t1
WHERE condition1 = 3 AND condition2 = 4
  AND EXTRACT(MONTH FROM "date") = 1
  AND NOT EXISTS (SELECT * FROM tablename AS t2
                  WHERE t2.col1 = t1.col1
                    AND t2."date" > t1."date")

BTW, DATE is a Teradata reserved word (https://en.wikipedia.org/wiki/SQL_reserved_words). If you have a column with that name, it must be delimited as "date".

Edit: "sorry, I forgot to mention the year:( it should extract data for the month January and year 2020."

SELECT col1, condition1, condition2, "date"
FROM tablename AS t1
WHERE condition1 = 3 AND condition2 = 4
  AND "date" BETWEEN DATE'2021-01-01' AND DATE'2021-01-31'
  AND NOT EXISTS (SELECT * FROM tablename AS t2
                  WHERE t2.col1 = t1.col1
                    AND t2."date" > t1."date")