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.