Querying single oldest record for each ID

Question

Assume that I have a database with the columns foo, bar, creationdate and data.

What I need, is a query, that queries the the first row that was created (i.e. where creationdate is the lowest) for each unique combination of foo and bar.

For example, for the following rows:

+-----+-----+--------------+----------------+
| foo | bar | creationdate | data           |
+-----+-----+--------------+----------------+
| abc | def | 31.12.2019   | some data      |
+-----+-----+--------------+----------------+
| abc | def | 01.05.2020   | other data     |
+-----+-----+--------------+----------------+
| ghi | jkl | 04.05.2020   | more data      |
+-----+-----+--------------+----------------+
| ghi | jkl | 05.05.2020   | even more data |
+-----+-----+--------------+----------------+

Then the desired output is:

+-----+-----+--------------+----------------+
| abc | def | 31.12.2019   | some data      |
+-----+-----+--------------+----------------+
| ghi | jkl | 04.05.2020   | more data      |
+-----+-----+--------------+----------------+

What would be the SQL query that would give me the desired result? I am using Postgres.

Answer 1

This is typically done using the proprietary distinct on () in Postgres

select distinct on (foo,bar) foo, bar, creationdate, data
from the_table
order by foo,bar,creationdate;

Answer 2

Try the following, you can achieve it by using window function row_number(). You may need to change your creationDate format. here is the demo.

select
    foo,
    bar,
    creationDate
from
(
    select
        foo,
        bar,
        creationDate,
        row_number() over (partition by foo, bar order by creationDate) as rnk
    from yourTable
) val
where rnk = 1