patternMinor

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thepreviousbarfoowithgettingvalueforhigherclosest

Problem

From this initial data:

select level as foo, mod(ora_hash(level),4) as bar from dual connect by level

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?

Solution

with data as (
   select level as foo, mod(ora_hash(level),4) as bar from dual connect by level cv(bar)]
      );

Code Snippets

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

Context

StackExchange Database Administrators Q#14743, answer score: 4

Revisions (0)

No revisions yet.