Re: ORDER BY + ROWNUM error

Nice example - however, In Oracle8i Release 2 (8.1.6) onwards, you can of course use the RANK and DENSE_RANK functions

SELECT deptno, ename, sal, comm,

   RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) as rk    FROM emp;

    DEPTNO ENAME SAL COMM RK ---------- ---------- ---------- ---------- ----------

        10 KING             5000                     1
        10 CLARK            2450                     2
        10 MILLER           1300                     3
        20 SCOTT            3000                     1
        20 FORD             3000                     1
        20 JONES            2975                     3
        20 ADAMS            1100                     4
        20 SMITH             800                     5
        30 BLAKE            2850                     1
        30 ALLEN            1600        300          2
        30 TURNER           1500          0          3
        30 WARD             1250        500          4
        30 MARTIN           1250       1400          5
        30 JAMES             950                     6

SELECT dname, ename, sal, DENSE_RANK()

   OVER (PARTITION BY dname ORDER BY sal) as drank    FROM emp, dept
   WHERE emp.deptno = dept.deptno
   AND dname IN ('SALES', 'RESEARCH');

DNAME ENAME SAL DRANK -------------- ---------- ---------- ----------

RESEARCH       SMITH             800          1
RESEARCH       ADAMS            1100          2
RESEARCH       JONES            2975          3
RESEARCH       FORD             3000          4
RESEARCH       SCOTT            3000          4
SALES          JAMES             950          1
SALES          MARTIN           1250          2
SALES          WARD             1250          2
SALES          TURNER           1500          3
SALES          ALLEN            1600          4
SALES          BLAKE            2850          5

in article kohqjtcoqe49mqm0gud7glbv5v3mvqo981_at_4ax.com, Dino Hsu at dino1_at_ms1.hinet.net wrote on 6/29/01 8:29 PM:

> 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