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

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/10/13
Message-ID: <34427d95.5105078_at_www.sigov.si>#1/1


On Mon, 13 Oct 1997 12:17:44 +0100, Ken Nichols <knichols_at_mcsilo.ilo.dec.com> wrote:

>The following will work:
>
>select rating from rating
>where rownum = 1
>order by rdate desc;
>
>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.
>
>Regards,
>
>Ken

Regards,


Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================
The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Oct 13 1997 - 00:00:00 CEST

Original text of this message