Re: ORDER BY + ROWNUM error

When there are ties, the ranking may not be what we want:

  1 select a.empno, a.sal, count(b.empno) from   2 (select empno, sal from emp) a,
  3 (select empno, sal from emp) b
  4 where a.sal<=b.sal
  5 group by a.empno, a.sal
  6* order by count(b.empno)
SQL> /     EMPNO SAL COUNT(B.EMPNO)
--------- --------- --------------

     7839      5000              1
     7788      3000              3
     7902      3000              3
     7566      2975              4
     7698      2850              5
     7782      2450              6
     7499      1600              7
     7844      1500              8
     7934      1300              9
     7521      1250             11
     7654      1250             11
     7876      1100             12
     7900       950             13
     7369       800             14

14 rows selected.

This one will solve the problem:

  1 select a.empno, a.sal, count(b.empno)+1 count from   2 (select empno, sal from emp) a,
  3 (select empno, sal from emp) b
  4 where a.sal<b.sal (+)
  5 group by a.empno, a.sal
  6* order by count(b.empno)
SQL> /     EMPNO SAL COUNT
--------- --------- ---------

     7839      5000         1
     7788      3000         2
     7902      3000         2
     7566      2975         4
     7698      2850         5
     7782      2450         6
     7499      1600         7
     7844      1500         8
     7934      1300         9
     7521      1250        10
     7654      1250        10
     7876      1100        12
     7900       950        13
     7369       800        14

14 rows selected.

Dino

On 29 Jun 2001 02:52:21 -0700, markg_at_mymail.tm (MarkyG) wrote:

>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 - 22:29:52 CDT