Getting the value of ‘foo’ for the closest previous row with higher ‘bar’

oracleoracle-10g-r2

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?

Best Answer

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)]
      );

Related Solutions

Oracle hierarchical query question (start with … connect by … )

There would have to be data indicating that the row is both a child of row A and a child of row B. This can be done, but does not seem like it would be typical. Think of an employee. Most employees only have one direct managers. In a simple design an employee could have two managers, but it would require multiple employee records. For example:

--drop table e;
create table e (id number, name varchar2(100), manager number);
insert into e values (1,'Amos',null);
insert into e values (2,'Bob',1);
insert into e values (3,'Cindy',1);
insert into e values (4,'Dave',2);
insert into e values (4,'Dave',3);
select substr(name,1,7) name, substr(prior name,1,7) Manager
   from e start with id=1 connect by prior id=manager order by name;

The simplest way of excluding the current row from the result of an analytic

There may be a simpler way, but here is a solution. If the current row is the minimum foo for the bar, then we always want the next (lead) foo. When there is only one foo for a bar the lead will always be null and for every other case we really do want the minimum foo for the bar.

with w as (select level as foo, decode(level,8,1,mod(level,4))*100 as bar
   from dual connect by level<9) 
select foo, bar
   , case 
        when foo = min(foo) over (partition by bar) 
           then lead(foo) over (partition by bar ORDER BY foo)
        else min(foo) over (partition by bar) 
     end pseudolowest
from w;

Unfortunately rows excluding current row doesn't exist.

Best Answer

Related Solutions

Oracle hierarchical query question (start with … connect by … )

The simplest way of excluding the current row from the result of an analytic

Related Question