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

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'));
> ==================

If your date in the table is always going to be the first of the month then
why not use the trunc function?

Like so:

select * from rating where rdate =
trunc(to_date('2/15/97','MM/DD/YY'),'MON');

In this way nothing is changed in the table data so that an index will always be used, if the rdate has one.

Hope this helps

Tom D. Received on Tue Oct 14 1997 - 00:00:00 CDT