CODEWITHSUNDEEP
oracle-databaseoracle11goracle10goracle-sqldeveloper
Vivek Ravi
Vivek Ravi

Reputation: 205

Select nth row from where the input condition is satisfied - Oracle database- Sqldbx

I am entirely new to oracle. I have a table having business days stored into it. I want to get 5th business day(5th row) from where my input condition is satified . For an example please consider below table

 BUSINESS DAY
8/06/2004 12:00:00 AM
8/07/2004 12:00:00 AM
8/08/2004 12:00:00 AM
8/09/2004 12:00:00 AM
8/10/2004 12:00:00 AM
8/13/2004 12:00:00 AM
8/14/2004 12:00:00 AM
8/15/2004 12:00:00 AM
8/16/2004 12:00:00 AM

Here my input date is 8/08/2004 12:00:00 AM my output should be 8/15/2004 12:00:00 AM. Please help to construct query for this. Please note that based on the requirement i may need past dates as well.

I referred other stack over flow question and answers as well. But it seems none of them are match with my question.

Upvotes: 1

Views: 72

Answers (2)

William Robertson
William Robertson

Reputation: 16001

Demo table:

create table business_days (busday) as
    select date '2004-08-06' from dual union
    select date '2004-08-07' from dual union
    select date '2004-08-08' from dual union
    select date '2004-08-09' from dual union
    select date '2004-08-10' from dual union
    select date '2004-08-13' from dual union
    select date '2004-08-14' from dual union
    select date '2004-08-15' from dual union
    select date '2004-08-16' from dual;

One approach:

select busday, t_plus_5
from   ( select busday
              , lead(busday,5) over (order by busday) as t_plus_5
         from   business_days
         where  busday >= date '2004-08-08' )
where   busday = date '2004-08-08';

Or this (requires Oracle 12.1 or later):

select busday
     , ( select d5.busday from business_days d5
         where  d5.busday >= d1.busday
         order by busday
         offset 5 rows fetch first row only ) as t_plus_5
from   business_days d1
where  busday = date '2004-08-08';

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142743

Here's one option: order business dates (with the ROW_NUMBER analytic function), and then apply simple arithmetic to it (i.e. add 5 days, or subtract some number of days):

SQL> with test (busday) as
  2    (select date '2004-08-06' from dual union
  3     select date '2004-08-07' from dual union
  4     select date '2004-08-08' from dual union
  5     select date '2004-08-09' from dual union
  6     select date '2004-08-10' from dual union
  7     select date '2004-08-13' from dual union
  8     select date '2004-08-14' from dual union
  9     select date '2004-08-15' from dual union
 10     select date '2004-08-16' from dual
 11    ),
 12  inter as
 13    (select busday, row_number() over (order by busday) rn
 14     from test
 15    )
 16  select i.busday
 17  from inter i
 18  where i.rn = (select i1.rn + 5 from inter i1
 19                where i1.busday = date '&par_busday'
 20               );
Enter value for par_busday: 2004-08-08

BUSDAY
----------
2004-08-15

SQL>

Upvotes: 1

Related Questions