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

On Mon, 13 Oct 1997 00:47:15 -0400, "Sid Soni" <soni_at_spam.mindspring.com> 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'));
>==================
>
>
>

the oracle concepts manual (I think it was) used an index_desc hint to achieve the same result.

create index fred on rating_tbl( rdate );

select /*+ index_desc( rating_tbl fred ) */ rating from rating_tbl
where rdate < to_date('2/15/97','MM-DD-YYYY') and rownum = 1;

although I have to say this is in my opinion a very dubious method. Received on Sun Nov 09 1997 - 00:00:00 CET