Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: %%How to <efficiently> do: SELECT MAX(Date)...WHERE Date < myParamDate

Re: %%How to <efficiently> do: SELECT MAX(Date)...WHERE Date < myParamDate

From: Ken Nichols <knichols_at_mcsilo.ilo.dec.com>
Date: 1997/10/13
Message-ID: <34420358.6C27@mcsilo.ilo.dec.com>#1/1

Yo Sid,

The following will work:

select rating from rating
where rownum = 1
order by rdate desc;

If you wanted to boost the performance of this query, you might even consider the hint which makes the first set of rows get returned faster, since all you're interested in is the first row, anwyway.

For example,

SELECT /*+ FIRST_ROWS */
        rating
FROM rating
where rownum = 1
order by rdate desc;

You may be able to prevent a full table scan if you have an index on rdate.

Regards,

Ken

Sid Soni wrote:
>
> Say I have a "rating history" table like this:
>
> RATING RDATE
> ----------- ---------
> 5 01-JAN-97
> 4 01-FEB-97
> 3 01-MAR-97
>
> If i want to determine what the rating was at 2/15/97 (it was updated to 4
> on 2/1/97) I can do the following:
>
> select rating from rating_tbl where rdate =
> (select max(rdate)
> from rating_tbl
> where rdate < to_date('2/15/97','MM-DD-YYYY'));
>
> Can I be more efficient & do this w/o a subselect?
>
> ============
> Create table rating
> (id number,
> rating number,
> rdate date);
> /
> insert into rating values (1, 5 ,to_date('1/1/97','MM-DD-YYYY'));
> insert into rating values (1, 4 ,to_date('2/1/97','MM-DD-YYYY'));
> insert into rating values (1, 3 ,to_date('3/1/97','MM-DD-YYYY'));
> ==================
Received on Mon Oct 13 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US