Maxqueue Maxqueue -3 years ago 182
SQL Question

oracle lag function with group by

I have a query that ignores values that increase from the previous value. For example take the following table:

col1 col2 col3
5 1 A
4 2 A
6 3 A
9 4 B
8 5 B
10 6 B


Now take the following query:

select col1
from (select col1, lag(col1) over (order by col2) as prev_value
from test
)
where prev_value is null or prev_value > col1;


Now query works as expected in the fact that it ignores columns where col1 = 6,9 and 10 because the previous value is less. Now the problem i am trying to solve is for it to do this on a group by basis. So i would only want it to ignore previous values grouped by col3. So i DO want 6 and 10 ignored but NOT 9 because when grouped by col3 9 would not have previous value.

Any help would be greatly appreciated

Answer Source

I think you just want partition by:

select col1
from (select col1, lag(col1) over (partition by col3 order by col2) as prev_value
      from test
     ) 
where prev_value is null or prev_value > col1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download