# Getting the value of 'foo' for the closest previous row with higher 'bar'

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?

• 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)`. Mar 12, 2012 at 7:02
• It varies - the example is much simplified from my real world problem :-) Mar 12, 2012 at 7:03

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

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;
``````

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.

• 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`) Mar 13, 2012 at 13:49
• I didn't test it, and in none of the test data has `prev_foo` further back than one step. Mar 13, 2012 at 23:25
• Yes, that's my bad, sorry. Thanks for contributing. Mar 14, 2012 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;
``````
• It was fun to take a crack at. Mar 13, 2012 at 17:23