asked on

Return rows based on date

Hello

Sample data

COLA COLB COLC
Cpy A AA 01/02/2010
Cpy A AA 08/05/2010
Cpy A AA 11/02/2009
Cpy A AA 01/02/2011
Cpy B AAB 21/02/2011
Cpy B AA 08/05/2010
Cpy B AAB 21/03/2008
Cpy B AAZ 19/02/2010

variable :date
for this example assume the date is '20/01/2010'

I now want a query to run and then only select rows
where COLC date field equals the date variable and if not
to return the first date nearest to it (before & not after)
So, my returned data would now look as follows given variable date
'20/01/2010'

COLA COLB COLC
Cpy A AA 11/02/2009
Cpy B AAB 21/03/2008

Is this possible

ASKER CERTIFIED SOLUTION

enachemc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

try this :

select * from your_table where colc = '20-jan-2010'
union
select * from your_table x where x.colc < '20-jan-2010'
and not exists ( select 1 from your_table y where y.colc = '20-jan-2010' )

SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

You closed this to quick.

This should be much more efficient since it only hits the table once.

select colA, colB, colC from (
select colA, colB, colC, row_number() over(partition by cola order by colC desc) myrownum
from tab1 where colC <= to_date('20/01/2010','DD/MM/YYYY')
)
where myrownum = 1
/