Re: ORDER BY + ROWNUM error

Dino,

The ORDER BY clause is executed AFTER the ROWNUMs are assigned. This means that the ROWNUMs are assigned more or less at random, but certainly not on a way that is related to the ORDER BY clause.

The alternative is to open a cursor and to fetch only the first 3 records.

        Erwin

Dino Hsu wrote:
>
> Dear all,
>
> If the persons with top 3 salaray are required, the following SQL's
> will give wrong answers when ORDER BY and ROWNUM are used together.
>
> SQL> select empno, sal from emp order by sal desc;
>
> EMPNO SAL
> --------- ---------
> 7839 5000
> 7788 3000
> 7902 3000
> 7566 2975
> 7698 2850
> 7782 2450
> 7499 1600
> 7844 1500
> 7934 1300
> 7521 1250
> 7654 1250
> 7876 1100
> 7900 950
> 7369 800
>
> 14 rows selected.
>
> SQL> select empno, sal from emp where rownum<=3 order by sal desc;
>
> EMPNO SAL
> --------- ---------
> 7499 1600
> 7521 1250
> 7369 800
>
> Can anyone tell me why and how? Thanks in advance.
>
> Dino
Received on Thu Jun 28 2001 - 04:46:42 CDT