CODEWITHSUNDEEP
oracle-databasesortinghighest
Landon Statis
Landon Statis

Reputation: 849

Oracle - Finding the NEXT largest number

Say I have a list of dates & prices:

20170322    109.89
20170321    107.02
20170320    109.25
20170317    108.44
20170316    108.53
20170315    107.94
20170314    106.83
20170313    110.02
20170310    107.31
20170309    107.54
20170308    107.67
20170307    108.98

What I need is, from the most recent date: 20170322 (109.89), what is the FIRST date / price value that is HIGHER than the original value, which is 20170313 (110.02). Note these are in DESC order of the date

Been at this ALL day.

Upvotes: 0

Views: 1275

Answers (2)

user5683823
user5683823

Reputation:

Solution using the MATCH_RECOGNIZE clause (requires Oracle 12 and above).

I created the test data in a WITH clause. That is NOT PART OF THE SOLUTION; the SQL query begins after the WITH clause, at SELECT TICKER, ....

The question mark in the reluctant match in the PATTERN trips the JDBC driver, so this query can't be run from SQL Developer; it needs to be run in SQL*Plus or similar front-end. (The workaround is to change b*? to b* and to add to the DEFINE clause: b as b.price <= a.price.)

To illustrate more of the flexibility of MATCH_RECOGNIZE, I assumed there may be several "tickers", each with its inception date (earliest date with a price), and the query looks for the first occurrence of a price higher than the original one, per ticker.

with
     test_data ( ticker, dt, price ) as (
       select 'XYZ', to_date('20170322', 'yyyymmdd'), 109.89 from dual union all
       select 'XYZ', to_date('20170321', 'yyyymmdd'), 107.02 from dual union all
       select 'XYZ', to_date('20170320', 'yyyymmdd'), 109.25 from dual union all
       select 'XYZ', to_date('20170317', 'yyyymmdd'), 108.44 from dual union all
       select 'XYZ', to_date('20170316', 'yyyymmdd'), 108.53 from dual union all
       select 'XYZ', to_date('20170315', 'yyyymmdd'), 107.94 from dual union all
       select 'XYZ', to_date('20170314', 'yyyymmdd'), 106.83 from dual union all
       select 'XYZ', to_date('20170313', 'yyyymmdd'), 110.02 from dual union all
       select 'XYZ', to_date('20170310', 'yyyymmdd'), 107.31 from dual union all
       select 'XYZ', to_date('20170309', 'yyyymmdd'), 107.54 from dual union all
       select 'XYZ', to_date('20170308', 'yyyymmdd'), 107.67 from dual union all
       select 'XYZ', to_date('20170307', 'yyyymmdd'), 108.98 from dual
     )
select ticker, dt, price
from   test_data
match_recognize (
  partition by ticker
  order     by dt
  measures  c.dt as dt, c.price as price
  one row per match
  pattern   ( ^ a b*? c )
  define    c as c.price > a.price
)
;

TICKER  DT            PRICE
------  ----------  -------
XYZ     2017-03-13   110.02

1 row selected.

Upvotes: 0

user5683823
user5683823

Reputation:

Assuming the columns are called DT and PRICE, and assuming there is only one "thing" whose price you monitor (otherwise you would need a GROUP BY clause):

select min(dt) as dt, min(price) keep (dense_rank first order by dt) as price
from   your_table
where  price > ( select min(price) keep (dense_rank first order by dt)
                 from   your_table
               )

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm

Upvotes: 1

Related Questions