Search code examples
sqlimpala

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
Solution

  • You can use GROUP BY query like

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