(classic ?) TOP 50 problem

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 - 13:07:25 CDT