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

From: Sid Soni <soni_at_spam.mindspring.com>
Date: 1997/10/13
Message-ID: <61s8pf$vig@camel18.mindspring.com>#1/1

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