PSQL: Ignore duplicates, show most recent records in table-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

I would say that your query is something I'm missing here... Anyway. You can sort, and pick first, you can group and pick max/min. That are simple solutions. Bigger cannon here are window functions.

If that is not enough - provide your query, I'll edit answer... we will get there! :)