How to pull distinct rows based on max timestamp?

Question

So I have some data like follows:

ID    group                 timestamp
001      AA   2021-03-23 22:48:34.879
002      XT   2021-03-24 01:18:34.879
002      BB   2021-03-25 22:42:34.879
003      SW   2021-03-25 12:53:34.879
003      Fe   2021-03-25 14:37:34.879
003      AA   2021-03-25 13:26:34.879

And I just want to condense it to the max timestamp for each ID and bring on the appropriate Group as well. This means I want every since ID in the table but only once, and the one iteration is the row with the most recent timestamp

Here's what I want it to look like:

ID    group                 timestamp
001      AA   2021-03-23 22:48:34.879
002      BB   2021-03-25 22:42:34.879
003      Fe   2021-03-25 14:37:34.879

I thought this code would work but it really isn't.....

SELECT ID, group, MIN(TIMESTAMP) as last_time
FROM tbl
GROUP BY ID, group

score 1 · Accepted Answer · answered Apr 08 '21 at 00:16

You can add a derived table to your where clause, like this

SELECT *
FROM a
WHERE a.TIMESTAMP IN (
        SELECT max(TIMESTAMP)
        FROM a AS b
        WHERE a.GROUP = b.GROUP
        )



    ID  group   timestamp
    2   XT  2021-03-24 01:18:35
    2   BB  2021-03-25 22:42:35
    3   SW  2021-03-25 12:53:35
    3   Fe  2021-03-25 14:37:35
    3   AA  2021-03-25 13:26:35

https://www.db-fiddle.com/f/jAqnWSkBKkw9CrPMWqmG66/0

score 0 · Answer 2 · answered Apr 08 '21 at 00:18

Wait, you said MAX timestamp but your query uses MIN?

Anyway, the quickest way is to make your (slightly modified) original query as sub-query then join it with the same table whereby the ON condition is matching ID and timestamp:

SELECT A.* 
FROM tbl A JOIN
(SELECT ID, MIN(TIMESTAMP) as last_time /*maybe MAX(timestamp)?*/
FROM tbl
GROUP BY ID) B
ON A.ID=B.ID AND A.timestamp=B.last_time ;

Here's a fiddle

How to pull distinct rows based on max timestamp?

2 Answers2