AWS athena - Get unique device row data for latest timestamp

Question

i have a table in aws athena as shown below.

and i want to get rows for each device id with highest timestamp.

Answer 1

With ansi-sql you just need a sub-select with a group by and aggregation function:

SELECT distinct last_record.*
FROM changesets AS c
INNER JOIN (
    SELECT 
        c2.user,
        MAX(c2.created_at) AS max_timestamp
    FROM changesets AS c2
    GROUP BY  c2.user
) AS last_record
    ON c.user = last_record.user

And of course don't forget if deviceId is not the primary-key or non-unique this will return multiple rows from last_record so you will need the distinct