CODEWITHSUNDEEP
sqlpostgresql
Owen
Owen

Reputation: 169

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

Upvotes: 1

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 2

Related Questions