E.g. Let's say we have columns a,b,c,b. We want a,b matching min(c) while partitioning by d.
crmmbqt=# create table demo (a int, b int, c int, d int);
crmmbqt=# insert into demo (a, b, c, d) values (1,2,1,1), (2,1,3,1), (3,4,5,2), (4,3,4,2);
INSERT 0 4
Time: 6.106 ms
crmmbqt=# select * from demo;
┌───┬───┬───┬───┐
│ a │ b │ c │ d │
├───┼───┼───┼───┤
│ 1 │ 2 │ 1 │ 1 │
│ 2 │ 1 │ 3 │ 1 │
│ 3 │ 4 │ 5 │ 2 │
│ 4 │ 3 │ 4 │ 2 │
└───┴───┴───┴───┘
(4 rows)
crmmbqt=# select first_value(a) over (partition by d order by c) as a, first_value(b) over (partition by d order by c) as b, c, d from demo;
┌───┬───┬───┬───┐
│ a │ b │ c │ d │
├───┼───┼───┼───┤
│ 1 │ 2 │ 1 │ 1 │
│ 1 │ 2 │ 3 │ 1 │
│ 4 │ 3 │ 4 │ 2 │
│ 4 │ 3 │ 5 │ 2 │
└───┴───┴───┴───┘
(4 rows)
NB: The use of first_value() is really important here.
For example, if you want to get max(c) instead of min(c), do not use last_value(a) but instead:
first_value(a) over (partition by d order by c desc)crmmbqt=# select min(a) as a, min(b) as b, min(c), d from (select first_value(a) over (partition by d order by c) as a, first_value(b) over (partition by d order by c) as b, c, d from demo) as foo group by d;
┌───┬───┬─────┬───┐
│ a │ b │ min │ d │
├───┼───┼─────┼───┤
│ 1 │ 2 │ 1 │ 1 │
│ 4 │ 3 │ 4 │ 2 │
└───┴───┴─────┴───┘
(2 rows)
