Re: Quesry question

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:33:33 CDT