CODEWITHSUNDEEP
sqlpostgresqlgroup-byaggregate
piotrek
piotrek

Reputation: 14550

postgres: aggregate function on multiple fields

My table is:

price: numeric, time: timestamp, type: integer

i want to group by type and for each group find max price and earliest (by time) price.

from the computational perspective, it's a simple linear / reduce-like operation. but how can it be done in postgres? is there some existing function like that? do i have to create my own aggregate? should i encode two field into one like $time-$price and just find minimum from it?

Upvotes: 1

Views: 247

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

Hmmm. Postgres doesn't have a first() aggregation function, but you can use arrays instead:

select type,
       max(price),
       array_agg(price order by time asc)[1] as earliest_price
from t
group by type;

EDIT:

There are other approaches, such as:

select distinct on (type) type,
       max(price) over (partition by type),
       price
from t
order by type, time asc;

Upvotes: 1

Related Questions