Search code examples
sqlpostgresqlgreatest-n-per-group

PSQL: Ignore duplicates, show most recent records in table

I have psql table with the following scheme. Each instance appears several times in the table, with a creation date.

sample CSV

I am interested only in the most recent ones. So filtering duplicates, show only unique ones with most recent occurrence.

My desired output would look like:

output CSV

Could you help me to find the proper query code?

I appreciate your help in advance!

Solution

  • You can use distinct on(unique_id) :

    select distinct on(unique_id) unique_id, "text", creation_date
from table_name
order by unique_id, creation_date desc;

    Fiddle