CODEWITHSUNDEEP
oracle-database
Shreyas K C
Shreyas K C

Reputation: 140

Getting the previous occurrence and next occurrence in oracle

Hi I am working on oracle DB. The DB has one column date. It consists of the dates of 5 years with the dates model will be refreshed. Ex

DATE_TABLE

DATE 
------------
1-jan-2013
15-jan-2013
31-jan-2013
6-feb-2013
etc.........

now for today's date suppose 13th jan 2013. The next refresh date will be 15th jan. and previous refresh date is 1st jan. to retrieve these two dates. Can i have any way without using PL/SQL. using regular select queries?. Thanks in advance

Upvotes: 3

Views: 395

Answers (2)

Shreyas K C
Shreyas K C

Reputation: 140

We can simply use the below query, plain and simple. No need of pl/sql

SELECT MIN(DATE) FROM DATE_TABLE WHERE DATE > SYSDATE ;

Upvotes: 1

Nick Krasnov
Nick Krasnov

Reputation: 27251

There are two functions LAG() (allows you to reference previous record) and LEAD() allows you to reference next record. Here is an example:

SQL> with t1(col) as(
  2    select '1-jan-2013'  from dual union all
  3    select '15-jan-2013' from dual union all
  4    select '31-jan-2013' from dual union all
  5    select '6-feb-2013'  from dual
  6  )
  7  select col                            as current_value
  8       , lag(col, 1) over(order by col) as prev_value
  9       , lead(col, 1) over(order by col)as next_value
 10    from t1
 11  ;

Result:

CURRENT_VALUE   PREV_VALUE    NEXT_VALUE
-------------   -----------   -----------
1-jan-2013      NULL          15-jan-2013
15-jan-2013     1-jan-2013    31-jan-2013
31-jan-2013     15-jan-2013   6-feb-2013
6-feb-2013      31-jan-2013   NULL

Upvotes: 4

Related Questions