Re: (classic ?) TOP 50 problem

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:25:46 CDT