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

In article <61s8pf$vig_at_camel18.mindspring.com>,   "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'));
> ==================

Hi,

Subqueries are not as bad as people normally think thanks to query optimization. Your query with subselect seems quite efficient to me.

Dwayne

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet