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

You could add a r_end_date column (and an index on it), putting an incredibly large date in the 'current' rating.

select rating from rating_tbl
 where r_end_date >= given_date
 and rdate <= given_date;

You should try one of your insert statements. I can hardly believe you did.

-- 
------------
Kind reGards
     \ /   |
      X    |
     / \   x
     Gerard

Sid Soni <soni_at_spam.mindspring.com> schreef in artikel
<61s8pf$vig_at_camel18.mindspring.com>...


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

> ==================

> 

> 

> 

>