When I answered this thread
1110234
I made this question.
I am using Oracle11gR2.
I want to sum Vals while LV is greater than its LV of target row at order by sorkKey.
Ex If LV is 2,sumVal is sum of between that row and another row which LV > 2.
MyTable
sortKey LV Val
------- -- ---
1 2 10
3 3 20
5 4 30
6 3 40
7 4 50
9 2 60
15 3 70
16 4 80
with MyTable(sortKey,LV,Val) as(
select 1,2,10 from dual union
select 3,3,20 from dual union
select 5,4,30 from dual union
select 6,3,40 from dual union
select 7,4,50 from dual union
select 9,2,60 from dual union
select 15,3,70 from dual union
select 16,4,80 from dual)
expected Output
sortKey LV Val sumVal
------- -- --- ------
1 2 10 150 (10+20+30+40+50)
3 3 20 50 (20+30)
5 4 30 30 (30)
6 3 40 90 (40+50)
7 4 50 50 (50)
9 2 60 210 (60+70+80)
15 3 70 150 (70+80)
16 4 80 80 (80)
If using subQuerys,solution is below.
But this is very complex :-(
My question is "Is there more simple solution?"
For ex using OLAP fuction,model clause etc...
with MyTable(sortKey,LV,Val) as(
select 1,2,10 from dual union
select 3,3,20 from dual union
select 5,4,30 from dual union
select 6,3,40 from dual union
select 7,4,50 from dual union
select 9,2,60 from dual union
select 15,3,70 from dual union
select 16,4,80 from dual)
select sortKey,LV,Val,
(select sum(b.Val)
from MyTable b
where a.sortKey <= b.sortKey
and b.sortKey <
(select nvl(min(c.sortKey),99999)
from MyTable c
where c.sortKey > a.sortKey
and c.LV <= a.LV)) as sumVal
from MyTable a;