In SQL we have the function MIN_BY(B,C), which returns the value of B at the minimum of C.
How would one get the same functionality, but without using the MIN_BY function?
i.e. given columns A,B,C, I want to group by A and return the value of B that corresponds to the minimum of C. I can see there must be some way to do it using OVER and PARTITION BY but am not well versed in enough to see how!
One method uses window functions:
select a, min(min_bc)
from (select t.*, min(b) over (partition by a order by c) as min_bc
from t
)
group by a;
Just to understand:
SETUP:
create table Test (a int, b int, c int);
insert into test values(1,2,3);
insert into test values(1,3,2);
insert into test values(1,4,1);
insert into test values(2,4,5);
insert into test values(2,8,4);
QUERY(min(b) for the case of multiple rows with minimum of c):
select a, min(b) from Test t
where c = (select min(c) from Test b where b.a = t.a)
group by a
RESULT:
A MIN(B)
1 4
2 8
RESULT of Gordon Linoffs query:
A MIN(MIN_BC)
1 2
2 4
Who's right, who's wrong and why
If you don't want to use subquery or window functions, I wrote an alternative here (Works best for INT types as the comparator):
