CODEWITHSUNDEEP
sqldatabaseoracle-database
Maxqueue
Maxqueue

Reputation: 2444

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

Upvotes: 4

Views: 7153

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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;

Upvotes: 8

Related Questions