Get the max date time in a table with multiple entries for each date

I have a table that has

Location | IsBroken | Date
AZ          1         2019-01-01 12:00
CA          0         2019-01-01 12:00
NY          1         2019-01-01 12:00
AZ          1         2019-01-01 15:00
CA          0         2019-01-01 15:00
NY          1         2019-01-01 15:00
AZ          1         2019-01-01 19:00
CA          0         2019-01-01 19:00
NY          1         2019-01-01 19:00
AZ          1         2019-01-02 14:00
CA          0         2019-01-02 14:00
NY          1         2019-01-02 14:00
AZ          1         2019-01-02 16:00
CA          0         2019-01-02 16:00
NY          1         2019-01-02 16:00
AZ          1         2019-01-03 12:00
CA          0         2019-01-03 12:00
NY          1         2019-01-03 12:00
AZ          1         2019-01-03 17:00
CA          0         2019-01-03 17:00
NY          1         2019-01-03 17:00

And I only want one row per date, preferably the max, so the result should be

AZ          1         2019-01-01 19:00
CA          0         2019-01-01 19:00
NY          1         2019-01-01 19:00
AZ          1         2019-01-02 16:00
CA          0         2019-01-02 16:00
NY          1         2019-01-02 16:00
AZ          1         2019-01-03 17:00
CA          0         2019-01-03 17:00
NY          1         2019-01-03 17:00

I’ve tried using a nested query in where:

WHERE foo.Date = (SELECT MAX(Date) FROM foo)

but it only gives me back 1 row.

The table will have continuous dates also, like

2019-01-02 
2019-01-03

etc.

And I need results for each date.

Answer

use corelated subquery

select t1.* from table_name t1
where t1.date= ( select max(date) from table_name t2 where t1.location=te.location and t1.date=t2.date)

you can do it using row_number() if your dbms support

select * from (select *,row_number()over(partition by location,Date order by date desc) rn
from table_name
) a where a.rn=1

Advertisement

Answer