Hi,
I am trying to build a query where based on a given input date pa_credat, the following logic should be applied:
- Find the ID of the first (chronologically) record in a table where credat >= pa_credat.
- If no such record exists, find the first record where credat < pa_credat. First here means closest in time to pa_credat.
Think of the records in the table as a timeline. Given a certain date, I need to find the closest record on or after that date and if that does not exist, find the closest record before that date.
I am trying to do all of this in one sql statement, but I think it is neither elegant nor performant (on a large dataset):
select objectid
from ( select objectid
from ( select objectid, credat
from ( select objectid, credat
from mytable
where credat >= pa_credat
order by credat asc )
where rownum = 1
union all
select objectid, credat
from ( select objectid, credat
from mytable
where credat < pa_credat
order by credat desc )
where rownum = 1 )
order by credat desc )
where rownum = 1
Is there a better approach to this problem?
Here's some sample data:
create table mytable
( objectid integer,
credat date );
insert into mytable values (1,to_date('01-03-2011','DD-MM-YYYY'));
insert into mytable values (2,to_date('02-03-2011','DD-MM-YYYY'));
insert into mytable values (3,to_date('03-03-2011','DD-MM-YYYY'));
insert into mytable values (4,to_date('04-03-2011','DD-MM-YYYY'));
insert into mytable values (5,to_date('05-03-2011','DD-MM-YYYY'));
insert into mytable values (6,to_date('06-03-2011','DD-MM-YYYY'));
insert into mytable values (7,to_date('07-03-2011','DD-MM-YYYY'));
The following input/output is expected
01-01-2011 -> objectid = 1
01-03-2011 -> objectid = 1
03-03-2011 -> objectid = 3
01-04-2011 -> objectid = 7
My db version is 10.2.0.4.0.
Thank you very much!