postgresql: group by columns/windows function/min-max and complex query

user_19347 0
Viewed 78

Imagine I've invoices from two branches. I need to select min and max invoice date and on that date show branch id. If at min/max date several branches have invoices, choose any.

CREATE TEMP TABLE invoice (
   id int not null,
   branch_id int  not null,
   c_date date  not null,
   PRIMARY KEY (id)
);

insert into invoice (id, branch_id, c_date) values
    (1, 1, '2020-01-01') 
    ,(2, 2, '2020-01-01')
    ,(3, 1, '2020-01-02')
    ,(4, 2, '2020-01-02')
    ,(5, 2, '2020-01-03');

The straightforward solution is (skip max part to do not overcomplicate the query).

select i2.branch_id, i2.c_date from (
    select min(i1.id) minid
    from (select min(i.c_date) mind, max(i.c_date) maxd 
        from invoice i
        )a
        join invoice i1
            on a.mind=i1.c_date) b 
    join invoice i2 on b.minid=i2.id

Window function solution a bit simpler but awkward too. Please keep in mind that the actual query is more complex, and I provide only the core part.

select * from (
    select a.branch_id, a.c_date from(
        select *, rank() over (order by c_date) r from invoice i
    ) a where a.r=1
    limit 1
) mn,
(select a.branch_id, a.c_date from(
        select *, rank() over (order by c_date desc) r from invoice i
    ) a where a.r=1
    limit 1
) mx

Any guesses on how to write the query more elegantly?

window-functions group-by postgresql sql
1 Answers

One method is a trick using arrays:

select min(date),
       (array_agg(branch_id order by date))[1] as first_branch,
       max(date),
       (array_agg(branch_id order by date desc))[1] as last_branch
from invoice;

This does aggregate all values into an array, so you wouldn't want to use this if there are too many values in each result row.

user_11440350
Related