Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Return rows based on date

Posted on 2011-02-11
Medium Priority
322 Views
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
0
Question by:philsivyer
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 12

Accepted Solution

enachemc earned 1600 total points
ID: 34869591
select * from tbl where colc = (select max(t.colc) from tbl t where t.colc <= 'your date here');
0

LVL 28

Expert Comment

ID: 34869657
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' )
0

LVL 28

Assisted Solution

Naveen Kumar earned 400 total points
ID: 34869669
just modified a bit more to use the cola as you need one date for each value of cola right ?

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' and x.cola = y.cola)
0

LVL 77

Expert Comment

ID: 34870917
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
/
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
###### Suggested Courses
Course of the Month8 days, 18 hours left to enroll