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

On Mon, 10 Nov 1997 22:10:47 GMT, nospam_at_rmjcs.demon.co.uk (Rhys Jones) wrote:

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

I was only looking at oracle sql solutions.

The DMAX.. solution you propose is exactly equivalent to the sub-query originally posted. Access would probably convert it to two sql statements to send to the Oracle database.

The select top 1 quey I imagine is the same as declaring a cursor and fetching only the first row, which could also be done using a pl/sql procedure, but the order by will still mean that all rows before 02/15/97 will be retrieved from the database before the sort. This can result in performance problems where you have a historical table e.g. with 5years data, you would be retieving and sorting up to 5*365 rows. Received on Tue Nov 11 1997 - 00:00:00 CST