CODEWITHSUNDEEP
sqlpostgresql
ℕʘʘḆḽḘ
ℕʘʘḆḽḘ

Reputation: 19375

select the last value after a double grouping

Consider the following example

timestamp                  mygroup   value   time_agg
2019-02-02 10:00:00.123    A       10      2019-02-02 10:00:00
2019-02-02 10:00:00.124    A       11      2019-02-02 10:00:00
2019-02-02 10:00:00.125    B       100     2019-02-02 10:00:00
2019-02-02 10:00:00.124    B       200     2019-02-02 10:00:00

I am trying to essentially "resample" this database.

That is, for each combination of time_agg and mygroup, I want the row with the latest timestamp.

Expected output is:

timestamp                  mygroup   value   time_agg
2019-02-02 10:00:00.124    A       11      2019-02-02 10:00:00
2019-02-02 10:00:00.125    B       100     2019-02-02 10:00:00

I was able to do this using the DISTINCT ON syntax but this seems quite slow. Is there something better?

SELECT DISTINCT ON(mygroup, time_agg) mygroup, time_agg, timestamp
FROM THISDATA
ORDER BY mygroup, time_agg DESC, timestamp DESC

Thanks!

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

DISTINCT ON is usually the fastest way to do what you want.

For performance, I recommend an index on (group, time_agg desc, timestamp desc).

Upvotes: 1

Related Questions