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

Sid & Keith,

Have I missed something here, or wouldn't a combination of the Dlookup and DMax functions in Access Basic do what you want ?

DMax("RDate","Rating_TBL","[RDate]<=#2/15/97#")

will return the relevant date, and, assuming the RDATE values are unique;

DLookup("Rating","Rating_TBL","[RDate]=DMax(""RDate"",""Rating_TBL"",""[RDate]<=#2/15/97#"")")

will return the required rating. If you particularly want to use SQL you could use the TOP predicate something like this;

SELECT TOP 1 RATING FROM RATING_TBL
WHERE (RDATE<=#02/15/97#)
ORDER BY RDATE DESC; Any use? Apologies if I missed something important earlier in the thread!

Rhys.

On Sun, 09 Nov 1997 18:51:41 GMT, boulke_at_globalnet.co.uk (Keith Boulton) wrote:

>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.

Change 'nospam' in email address to 'rhys'.

###############################################################
# Rhys Jones                  #        rhys_at_rmjcs.demon.co.uk #
# Near Brighton, England      # http:\\www.rmjcs.demon.co.uk\ #
###############################################################