Re: SQL - SELECT x rows...

Hi all,

I had the same problem, and it turned out to be that only since Oracle 8.1.6 the order by clause is *allowed!!* in a view (yes, a subquery is actually an implicit view!). I was lucky that we had 8.1.6. So to get the 3 best payed employees out of the scott.emp table: select * from ( select * from emp order by sal desc ) where rownum <= 3; works only for 8.1.6 and up.. In any previous version of Oracle this is NOT allowed... Glad we have it now ;-)

Bastiaan Schaap
Oracle web development,
Desyde BV - Baarn
http://www.desyde.nl/
tel. +31355411711

Jim Lyons <jlyons4435_at_my-deja.com> wrote in message news:8mukog$qju$1_at_nnrp1.deja.com...
> In article <F4155D9F9AA4D111B9990020AFBA52D53E7D56_at_class06.ip061.sfi-
> software.com>,
> "Mark G" <markag_at_wonderstats.com> wrote:
>
> > SELECT column_list ROWNUM FROM
> > (SELECT column_list FROM table
> > ORDER BY Top-N_column)
> > WHERE ROWNUM <= N
>
> Does this work? We have Oracle 8 and the documentation says (and
> experimentation supports) that you cannot have an ORDER BY clause
> on a subquery (which is what from FROM part is).
>
> Does this require some sort of add-on? I went to oracle.com and tried
> to search on Top-N but all I got were javascript error messages. I
> notified Oracle of the problem, BTW.
>
> --
> Jim Lyons | Network Systems Analyst
> Entertech - The IC2 Institute | 512-482-0273, ext. 269
> University of Texas at Austin | jlyons_at_weblyons.com
> http://www.weblyons.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Aug 11 2000 - 00:00:00 CDT