SQL Query for Latest Snapshot From Bitemporal Data

Question

Given a table of data with bitemporal modeling, where there are 2 dates: (i) the date that the data applies to, and (ii) the date at which the fact was known.

City    Temperature  Date         As_of_Datetime
----    -----------  ----         --------------
Boston  32           2022/07/01   2022/06/28 13:23:00
Boston  31           2022/07/01   2022/06/29 12:00:00
Miami   81           2022/07/01   2022/06/28 13:23:00
Miami   85           2022/07/01   2022/06/29 12:00:00

What SQL query will give the latest snapshot of the data date based on the As_of_Datetime? e.g.

City    Temperature  Date         
----    -----------  ----         
Boston  31           2022/07/01   
Miami   85           2022/07/01   

Answer 1

select
  t1.*
from
  temperature_table t1,
  (select max(As_of_Datetime) as max_as_of, “City”, “Date” from temperature_table group by “City”, “Date”) t_temp
where
  t1.”City” = t_temp.”City” and t1.”Date” = t_temp.”Date” and t1.”As_of_Datetime” = t_temp.”max_as_of”
order by
  t1.”City”, t1.”Date”