Re: (classic ?) TOP 50 problem

Just one clarification, Sub Queries with ORDER BY is now supported in Oracle8 v8.1 (I think).

Andrew

John Higgins wrote:

> Since ORDER BY is not allowed in a sub-select, Oracle's parser thinks you
> must have forgotten the right parenthesis!
>
> Laurent Wantellet wrote:
>
> > I have the Oracle 8.0 DBMS.
> >
> > I want a top 50 and I've tried the (classic?) solution using numrow.
> > But it does not match with my request.
> >
> > Somebody can help me ?
> >
> > Let a table of pilots :
> >
> > create table pilots( pilotId number,flightLength number );
> > insert into pilots values ( 1,10 );
> > insert into pilots values ( 1,1238 );
> > insert into pilots values ( 1,47328 );
> > insert into pilots values ( 1,9832834 );
> > insert into pilots values ( 1,2831 );
> > insert into pilots values ( 1,4321 );
> > insert into pilots values ( 2,432 );
> > insert into pilots values ( 2,234729 );
> > insert into pilots values ( 2,432 );
> > insert into pilots values ( 2,432 );
> > insert into pilots values ( 2,43209 );
> >
> > To get the average of flight for each pilot ordered by the average,
> > there's no problem :
> >
> > SELECT pilotId,avg(flightLength) average
> > FROM pilots
> > GROUP BY pilotId
> > ORDER BY average DESC
> >
> > PILOTID AVERAGE
> > --------- ---------
> > 1 1648093.7
> > 2 55846.8
> >
> > Now, there are thousands of pilots and I want the top 50 ( here a top 1
> > ! ).
> > I'm using the classic rownum :
> >
> > SELECT pilotId,average
> > FROM
> > (
> > SELECT pilotId,avg(flightLength) average
> > FROM pilots
> > GROUP BY pilotId
> > ORDER BY average DESC
> > )
> > WHERE rownum <= 1
> > ;
> >
> > This produces the error :
> >
> > ORDER BY average DESC
> > *
> > ERROR at line 7:
> > ORA-00907: missing right parenthesis
> >
> > Thanks in advance for your help.
Received on Thu Apr 08 1999 - 22:59:42 CDT