Select one of duplicates in SQL by date

Question

I have duplicates like these in a table:

I can select the duplicates, but I need to select only the ones marked in yellow, aka: the oldest of the duplicates. How could I filter those?

Answer 1

You can use ROW_NUMBER() window function to identify the oldest record, and then just select that:

SELECT *
FROM 
    (
        SELECT yt.*, ROW_NUMBER() OVER (PARTITION BY REFERENCE ORDER BY CREATION DATE ASC) as rn
        FROM yt
    )
WHERE rn = 1;