By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 1 AS id, 2020 AS year UNION ALL
SELECT 2, 2020 UNION ALL
SELECT 3, 2020 UNION ALL
SELECT 1, 2019 UNION ALL
SELECT 2, 2019 UNION ALL
SELECT 2, 2019
),
cte AS (
SELECT id, year, DENSE_RANK() OVER (ORDER BY year) dr,
ROW_NUMBER() OVER (PARTITION BY year ORDER BY id) rn
FROM yourTable
)
SELECT id, year
FROM cte
WHERE dr = rn;
| id | year |
|---|---|
| 1 | 2019 |
| 2 | 2020 |