title: oracle lag function with group by
body: <p>I have a query that ignores values that increase from the previous value. For example take the following table:</p>
  
  <pre><code>col1    col2    col3
  5       1       A
  4       2       A
  6       3       A
  9       4       B
  8       5       B
  10      6       B
  </code></pre>
  
  <p>Now take the following query:</p>
  
  <pre><code>select col1
  from (select col1, lag(col1) over (order by col2) as prev_value
    from test
   ) 
  where prev_value is null or prev_value &gt; col1;
  </code></pre>
  
  <p>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.</p>
  
  <p>Any help would be greatly appreciated</p>