SQL select latest created datetime if updated datetime duplicate

user_17421874 0
Viewed 39

I would like to select TAG in condition:

  • if the latest updated_datetime is NULL, select created_datetime
  • select latest created_datetime if updated_datetime are duplicate

MY_TABLE

ID       updated_datetime     created_datetime       TAG
---------------------------------------------------------
2021095         null         2021-09-20 15:05:26     A
2021095         null         2021-09-21 15:05:26     A
2021096 2021-11-23 01:34:57  2021-09-20 15:05:26     B
2021096 2021-11-23 01:34:57  2021-11-23 01:27:15     B
2021096 2021-11-23 01:34:57  2021-11-23 01:34:57     B

Expected output:

ID       latest_datetime        FLAG
------------------------------------
2021095  2021-09-21 15:05:26     Y
2021096  2021-11-23 01:34:57     N

Can I use PARTITION BY for latest_datetime?

Please help me how can I write the code?

Thank you for your help

sql
1 Answers

Just a simple aggregation by the ID should do it

SELECT 
  ID
, MAX(COALESCE(latest_datetime, created_datetime)) AS latest_datetime
, MAX(CASE WHEN TAG = 'A' THEN 'Y' ELSE 'N' END) AS FLAG
FROM MY_TABLE t
GROUP BY ID
ORDER BY ID
user_40034190
Related