Skip to content
Advertisement

Query to find value in column dependent on a different column in table being the minimum date

I have a dataset that looks like this. I would like to pull a distinct id, the minimum date and value on the minimum date.

id  date         value
1   01/01/2020  0.5
1   02/01/2020  1
1   03/01/2020  2
2   01/01/2020  3
2   02/01/2020  4
2   03/01/2020  5

This code will pull the id and the minimum date

select Distinct(id), min(nav_date)
from table
group by id

How can I get the value on the minimum date so the output of my query looks like this?

id  date    value
1   01/01/2020  0.5
2   01/01/2020  3

Advertisement

Answer

Use distinct on:

select distinct on (id) t.*
from t
order by id, date;

This can take advantage of an index on (id, date) and is typically the fastest way to do this operation in Postgres.

3 People found this is helpful
Advertisement