Re: Quesry question

Well, that query WILL work, just it probably won't give you the results you were expecting. Essentially the rownum restriction will occur BEFORE the order by, therefore you will get ten ordered rows, which is not necessarily the same as ordering the entire resultset THEN returning the top 10.

John.

"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 - 17:36:09 CDT