Re: Quesry question

You are correct in part, the ORDER BY clause operates on the RETURNED rows, so the rownum restriction applies - the data returned, absent an index, or an order by clause, is in random ( or more correctly, non-defined) order..

"John Peterson" <johnp_at_azstarnet.com> wrote:

>When I conduct a little test:
>
>create table Test.Test(Num INTEGER);
>
>insert into Test.Test values (2);
>insert into Test.Test values (5);
>insert into Test.Test values (1);
>insert into Test.Test values (3);
>insert into Test.Test values (4);
>
>select * from Test.Test where rownum <= 3 order by Num;
>
>This does NOT work. :-(
>
>For some reason, the ROWNUM in the WHERE clause seems to be *restricting*
>the result set to a ROWNUM number of records *before* the ORDER BY clause
>takes effect. I'm assuming it's choosing those rows in the order that they
>were inserted. (I guess it sort of makes sense, but...)
>
>
>"John Peterson" <johnp_at_azstarnet.com> wrote in message
>news:tgahn6j1ppnj2b_at_corp.supernews.com...
>> Hello, John!
>>
>> I verified that what you're saying is correct, but can you explain WHY you
>> can't do the following successfully:
>>
>> select *
>> from scott.emp
>> where rownum <= 10
>> order by ename;
>>
>> I see that this doesn't work, but I'm unclear as to why.
>>
>> Thanks!
>>
>> John Peterson
>>
>> "John Edward Scott" <admin_at_NOdaedalus-soln.demon.co.ukSPAM> wrote in
 message
>> news:990199170.20480.0.nnrp-14.d4e495f5_at_news.demon.co.uk...
>> > Note that this method WON'T produce the required results if you're
>> > attempting to order the data. If you also want to use an order by clause
>> > you'll need to use a subselect, for example -
>> >
>> > select * from (select * from mytable order by mycolumn) where rownum <
 10;
>> >
>> > John.
>> >
>> > "Hubert" <h.lentaigne_at_free.fr> wrote in message
>> > news:aF6N6.1120$eA7.1547376_at_nnrp5.proxad.net...
>> > > Try
>> > >
>> > > Select * from <table> where rownum < 10
>> > >
>> > > Hubert
>> > >
>> > > "Cyb3rPunk" <a.merola_at_inwind.it> wrote in message
>> > > news:9e2r3n$pbg$1_at_fe2.cs.interbusiness.it...
>> > > > Hiya all!
>> > > >
>> > > > I have a quesry question...
>> > > >
>> > > > On my MS SQL server I have made this query:
>> > > >
>> > > > Select TOP 10 * from <table>
>> > > > and it returns the first 10 results (in the resultset) of the
 query..
>> > > >
>> > > > Now I have the same DB on Oracle 8i, but the query doesn't work
 because
 it
>> > > > seems not to accept the "TOP 10" instruction.
>> > > >
>> > > > Can someone help me?
>> > > >
>> > > > Bye
>> > > >
>> > > > Alex
>> > > >
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
Received on Fri May 18 2001 - 11:53:49 CDT