Re: Finding the 10 smallest values in a column
Date: 1997/11/14
Message-ID: <346c9777.39685775_at_newshost>#1/1
On Fri, 14 Nov 1997 16:21:31 +0100, Angelo Cavallaro <angelo.cavallaro_at_pcm.bosch.de> wrote:
>Tim Hall wrote:
>> Oh No, not again!
>>
>> Querying by ROWNUM with an order by D*O*E*S N*O*T W*O*R*K!!!
>>
>
>I tried to order by ROWNUM and it works !!!
>I work with ORACLE Release 7.1.5.2.3
>Maybe ORACLE changed it.
>Bye,
>Angelo.
Query by ROWNUM with an order by *can work by ACCIDENT* but it is certainly not guaranteed to work in the long run.
You probably have an index on the column in question. the column is NOT NULLable or you have a where clause that precludes the data. the optimizer is using the index to order the data. Since the index is sorted, the rownum trick is working.
the problem is that rownum is assigned before the sort takes place. If the data is coming out sorted (via an index) then it works. If the optimizer decides to change its mind at some point in the future, it will stop working.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Nov 14 1997 - 00:00:00 CET