Re: ORDER BY + ROWNUM error

From: MarkyG <markg_at_mymail.tm>
Date: 29 Jun 2001 02:52:21 -0700
Message-ID: <ab87195e.0106290152.61041227@posting.google.com>

Your experiments are indeed correct! You cannot have an order by in an inline view pre 8i.
Its a well known 'feature'.
Try...

SELECT COUNT(distinct b.empno) cnt, a.empno, a.sal FROM
(SELECT empno, sum(sal) sal FROM emp GROUP BY empno) a, (SELECT empno, sum(sal) sal FROM emp GROUP BY empno) b WHERE a.sal <= b.sal
HAVING COUNT(distinct b.empno) <=3
GROUP BY a.empno, a.sal
ORDER BY COUNT(distinct b.empno)

SQL> CNT EMPNO SAL
---------- ---------- ----------

         1        104     124435
         2        103      72746
         3        102      62347

HTH Mark

<snip>
>
>
> Result from my experiments: it works in Oracle8i, but not in Oracle8:
>
> 1 select empno,sal
> 2 from (select empno, sal from emp order by sal desc)
> 3* where rownum<=3
> SQL> /
> from (select empno, sal from emp order by sal desc)
> *
> ERROR at line 2:
> ORA-00907: missing right parenthesis
>
> Dino
Received on Fri Jun 29 2001 - 04:52:21 CDT