Skip to content
Advertisement

Returning the row with the most recent timestamp from each group

I have a table (Postgres 9.3) defined as follows:

The pertinent details here are the customer_id, the timestamp, and the licensekeys_checksum. There can be multiple entries with the same customer_id, some of those may have matching licensekey_checksum entries, and some may be different. There will never be rows with equal checksum and equal timestamps.

I want to return a table containing 1 row for each group of rows with matching licensekeys_checksum entries. The row returned for each group should be the one with the newest / most recent timestamp.

Sample Input:

Desired Output:

I have managed to piece together a query based on the comments below, and hours of searching on the internet. 🙂

It seems to work, but I am unsure. Am I on the right track?

Advertisement

Answer

Your query in the question should perform better than the queries in the (previously) accepted answer. Test with EXPLAIN ANALYZE.

DISTINCT ON is typically simpler and faster:

db<>fiddle here
Old sqlfiddle

Detailed explanation:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement