Edamame Edamame - 3 months ago 15
SQL Question

SQL/Impala : return records for non-duplicated object with the latest timestamp

In Impala/SQL, is it possible to have a query to find the records for non-duplicated objects with their corresponding latest timestamp?

For example, if I have

table_1
:

id | timestamp
-----------------------
1 | 2016-01-02
2 | 2016-02-01
1 | 2016-02-04
1 | 2016-03-05
3 | 2016-05-12
3 | 2016-05-15
4 | 2016-07-07
5 | 2016-08-01


I would like my query to return the data like below

id | timestamp
-----------------------
2 | 2016-02-01
1 | 2016-03-05
3 | 2016-05-15
4 | 2016-07-07
5 | 2016-08-01

Answer

You can use GROUP BY query like

select id, max(timestamp) as maxStamp
from table_1
group by id;