DISTINCT ON to find min and max times

Question

I have tried using DISTINCT ON with posrgresql to achieve the following: Lets say I have a table that looks like this:

id  time   price
1   12:00  10
1   13:00  20
1   14:00  30

And my goal is to create a table with only 1 row per id, that shows a column of the minimum time price and the maximum time price. Something that looks like this:

id  min_time_price  max_time_price
1   10              30

I tried using DISTINCT ON (id) but can't really get it.

Would love some help, Thank you!

Answer 1

Here is one method:

select t.id, tmin.price, tmax.price
from (select t.id, min(time) as min_time, max(time) as max_time
      from t
     ) t join
     t tmin
     on t.id = tmin.id and t.min_time = tmin.time join
     t tmax
     on t.id = tmax.id and t.max_time = tmax.time;

You can also use aggregation. Postgres doesn't have first()/last() aggregation functions, but arrays are handy:

select t.id,
       array_agg(price order by time asc)[1] as min_time_price,
       array_agg(price order by time desc)[1] as max_time_price
from t
group by id;

Or using first_value() and last_value():

select distinct t.id,
       first_value(price) over (partition by time order by time) as min_time_price,
       first_value(price) over (partition by time order by time desc) as max_time_price
from t