CODEWITHSUNDEEP
sqlpostgresqlgrafana
Parth
Parth

Reputation: 1266

Obtain latest record for a given second Postgres

I have data with millisecond precision timestamp. I want to only filter for the most recent timestamp within a given second. Ie. records (2020-07-13 5:05.38.009, event1), (2020-07-13 5:05.38.012, event2) should only retrieve the latter.

I've tried the following:

SELECT 
    timestamp as time, event as value, event_type as metric 
FROM 
    table
GROUP BY 
    date_trunc('second', time)

But then I'm asked to group by event as well and I see all the data (as if no group by was provided)

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

In Postgres, you can use distinct on:

select distinct on (date_trunc('second', time)) t.*
from t
order by time desc;

Upvotes: 1

Related Questions