Re: ORDER BY + ROWNUM error

From: palm <lisakor_at_hanmail.net>
Date: Thu, 28 Jun 2001 18:09:48 +0900
Message-ID: <9hes7f$hr$1@news1.kornet.net>

oracle engine parse and execute 'WHERE clause' first. so that result can be natural.
instead of that sql query, you can use this : SELECT * FROM (
SELECT empno, sql FROM emp ORDER BY sal DESC )
WHERE rownum < 4;

"Dino Hsu" <dino1_nospam_at_ms1.hinet.net> wrote in message news:80sljt0kfma6l34ev26p99uef0odlgrbib_at_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 - 04:09:48 CDT