Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

From this initial data:

select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9;
/*
FOO                    BAR                   
---------------------- ----------------------
1                      3                     
2                      2                     
3                      3                     
4                      3                     
5                      2                     
6                      0                     
7                      3                     
8                      2                      
*/

I want to get the value of foo, bar, prev_foo for each row, where prev_foo is the value of foo from the closest 'previous' row (defined by ordering on foo which is unique) such that bar in that row is greater than bar in the current row. In other words, I want this result:

/*
FOO                    BAR                    PREV_FOO                 
---------------------- ---------------------- ----------------------
1                      3                                           
2                      2                      1                   
3                      3                                          
4                      3                                           
5                      2                      4                     
6                      0                      5                    
7                      3                                           
8                      2                      7                     
*/

How do I achieve this?

share|improve this question
    
Is the range of possible values for bar always known prior to executing the statement or is it dependant on the statement? Your initial data suggests it's always in (0,1,2,3). –  René Nyffenegger Mar 12 '12 at 7:02
    
It varies - the example is much simplified from my real world problem :-) –  Jack Douglas Mar 12 '12 at 7:03

4 Answers 4

with data as (
   select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9
)
select
  foo,
  bar,
  prev_foo
from
  data
model 
      dimension by (foo, bar)
      measures     (cast (null as number) as prev_foo,
                    cast (null as number) as store_foo
                   )
      rules        (
        store_foo[any, any] = cv(foo),
        prev_foo [any, any] = max(store_foo) [foo < cv(foo), bar > cv(bar)]
      );
share|improve this answer
    
This is fascinating to me - I just know I need to learn the model clause :-) It works out slower in my testing than my solution however - the plan doesn't tell me much about why... –  Jack Douglas Mar 13 '12 at 13:55
    
Incidentally, Phil was wondering if you were happy for him to quote your blog like this? –  Jack Douglas Mar 13 '12 at 13:58
    
If not, I'll edit it! –  Phil Mar 13 '12 at 14:02
    
+1 There seems to be nothing the Model clause can't do if you can understand it. –  Leigh Riffel Mar 13 '12 at 15:58

Here is one way, not using analytics so pretty inefficient:

with w as (select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9)
select foo, bar, (select max(foo) from w where foo<ww.foo and bar>ww.bar) from w ww;
share|improve this answer

Untested:

with W as 
(select level as foo, mod(ora_hash(level),4) as bar 
   from dual 
connect by level<9)
select foo, bar, 
       case when prev_bar > bar then prev_foo else null end as prev_bar
  from (select foo, bar, 
               lag(foo) over (order by foo) as prev_foo,
               lag(bar) over (order by foo) as prev_bar
          from w)
 order by foo;

The with clause is the original query.

The inner most select uses analytics to get the previous (lag) value of foo and bar.

The outer query uses a case statement to implement your business rule.

share|improve this answer
    
This doesn't take account of cases where prev_foo is further back than 1 step - compare your results with mine or René's after increasing the size of the test data (eg connect by level<15) –  Jack Douglas Mar 13 '12 at 13:49
    
I didn't test it, and in none of the test data has prev_foo further back than one step. –  Adam Musch Mar 13 '12 at 23:25
    
Yes, that's my bad, sorry. Thanks for contributing. –  Jack Douglas Mar 14 '12 at 7:20

Here is another answer that works for nine rows, but not for more. I'm posting it in case it sparks an idea.

WITH w AS (SELECT level as foo, mod(ora_hash(level),4) as bar FROM dual CONNECT BY Level<9)
SELECT foo, bar, Case When Highest > Bar Then 
   MAX(foo) OVER (PARTITION BY Highest 
     ORDER BY foo RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
   Else NULL End prev_foo
FROM 
   (SELECT foo, bar, max(bar) OVER (
      ORDER BY foo RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Highest FROM w)
ORDER BY foo;  
share|improve this answer
    
I'm wrestling with this, thanks :-) –  Jack Douglas Mar 13 '12 at 17:18
    
It was fun to take a crack at. –  Leigh Riffel Mar 13 '12 at 17:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.