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

>>If you wanted to boost the performance of this query, you might even
>>consider the hint which makes the first set of rows get returned faster,
>>since all you're interested in is the first row, anwyway.
>>
>>SELECT /*+ FIRST_ROWS */
>> rating
>>FROM rating
>>where rownum = 1
>>order by rdate desc;
>
>Your suggestions are totaly wrong. ORDER BY sentence is allways
>applied only after the WHERE connditions have allready limited the
>return set! In your case, the query will find first row (whichever),
>then the ROWNUM=1 condition will stop returning further rows and then
>ORDER BY will sort this one row in ascending order :-) .
>
>For what you want to achive you should force the Oracle to perform
>implicit sorting before the rownum is evalueted - and this can't be
>done unless you have index and use hints (for descendin order) or you
>isue a subselect or join.
>
>>You may be able to prevent a full table scan if you have an index on
>>rdate.
>>

I'm sorry i have not read original message. But i have some oppinion on this problem. So if you have index on field( DATE type which you are asked ) You might write this one:
SELECT /*+ INDEX_DESC( myTable myDateIndexName ) */ date FROM ...
WHERE Date< myParamDate AND

                rownum= 1;

Think this may be you asked.

Bors/ Received on Sat Oct 18 1997 - 00:00:00 CDT