ORDER BY + ROWNUM error

From: Dino Hsu <dino1_nospam_at_ms1.hinet.net>
Date: Thu, 28 Jun 2001 16:56:35 +0800
Message-ID: <80sljt0kfma6l34ev26p99uef0odlgrbib@4ax.com>

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 - 03:56:35 CDT