CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Error_2646
Error_2646

Reputation: 3801

Find next lowest value over partition

I'm looking for a one-liner window function to get the next lowest value of Field2 over a partition of Field1 without a subquery.

Field1   Field2      Last_Val
A         1          Null
A         2          1
A         2          1
A         2          1
A         3          2
B         1          Null
B         7          1

I know how to do it a few ways, easiest being

  SELECT T1.Field1,
         T1.Field2,
         ( SELECT MAX(T2.Field2)
             FROM TEST_DATA T2
            WHERE T1.Field1 = T2.Field1
              AND T1.Field2 > T2.Field2
         ) last_val
    FROM TEST_DATA T1;

But I'm interested if it can be done in a single clause.

Upvotes: 1

Views: 425

Answers (2)

MT0
MT0

Reputation: 168351

You need a windowed analytic function with the RANGE clause:

Oracle Setup:

CREATE TABLE table_name ( Field1, Field2 ) AS
SELECT 'A', 1 FROM DUAL UNION ALL
SELECT 'A', 2 FROM DUAL UNION ALL
SELECT 'A', 2 FROM DUAL UNION ALL
SELECT 'A', 2 FROM DUAL UNION ALL
SELECT 'A', 3 FROM DUAL UNION ALL
SELECT 'B', 1 FROM DUAL UNION ALL
SELECT 'B', 7 FROM DUAL

Query:

select field1,
       field2,
       max(field2) over(
         partition by field1
         order by field2
         range between unbounded preceding and 1 preceding
       ) as last_val
from   table_name

Output:

FIELD1 | FIELD2 | LAST_VAL
:----- | -----: | -------:
A      |      1 |     null
A      |      2 |        1
A      |      2 |        1
A      |      2 |        1
A      |      3 |        2
B      |      1 |     null
B      |      7 |        1

db<>fiddle here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270533

If your values are integers, then this works:

with t as (
      select 'A' as field1, 1 as field2 from dual union all
      select 'A', 2 from dual union all
      select 'A', 2 from dual union all
      select 'A', 2 from dual union all
      select 'A', 3 from dual
     )
select t.*,
        max(field2) over (partition by field1 order by field2 range between unbounded preceding and 1 preceding)
from t;

Upvotes: 1

Related Questions