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

"Sid Soni" <soni_at_spam.mindspring.com> writes:

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

Assuming Visual Basic/Access SQL:
(I saw this post in comp.lang.basic.visual.misc)

parameters [when] datetime;
select top 1 rating_tbl.* from rating_tbl where [when] is null or [when] >= rating_tbl.rdate order by rdate desc;

[snip]

-- 
Joe Foster <mailto:joe*AT*bftsi0.gate.net> Spam is irrelevant. Assimilate this:
<ftp://ftp.microsoft.com/softlib/index.txt> Microsoft's master patch list
<ftp://ftp.microsoft.com/softlib/mslfiles/> MS I got yer patch right here!
<http://www.microsoft.com/kb/default.asp>   MS Knowledge Base
<http://www.dejanews.com>                   Usenet search and archival engine
<http://www.altavista.digital.com>          WWW *and* Usenet search engine
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!