sql - Getting lowest values in GROUP BY -

with test_data (   select 1 key, 1 a, 2 b dual union  --# lowest key=1   select 1 key, 2 a, 1 b dual union   select 2 key, 3 a, 3 b dual union  --# lowest key=2, lowest b   select 2 key, 3 a, 4 b dual union   select 2 key, 4 a, 5 b dual ) 

i'm trying group key, , retrieve lowest a , corresponding b (lowest b in case of ties), plus sum of bs:

       key             first_b      sum_b ---------- ---------- ---------- ----------          1          1          2          3          2          3          3         12 

i can realize sub-select

select key, min(a) a, first_b, sum(b) sum_b (   select key, a, b,          first_value(b) on (partition key order a, b) first_b   test_data ) group key, first_b 

but wonder if there way avoid sub-select, like

select key, a, sum(b) sum_b,        min( first_value(b) on (partition key order a, b) ) first_b test_data group key, 

which raises ora-30483: window functions not allowed here

---

thanks in advance, peter

are looking first/last aggregate functions? because of min function, ordering b on first_b column should redundant depends on how intend on using it.

with test_data (   select 1 key, 1 a, 2 b dual union  --# lowest key=1   select 1 key, 2 a, 1 b dual union   select 2 key, 3 a, 3 b dual union  --# lowest key=2, lowest b   select 2 key, 3 a, 4 b dual union   select 2 key, 4 a, 5 b dual ) select    key,    min(a) a,   min(b) keep (dense_rank first order a, b) first_b,   sum(b) sum_b test_data group key