Re: ORDER BY + ROWNUM error

From: Dino Hsu <dino1_nospam_at_ms1.hinet.net>
Date: Fri, 29 Jun 2001 13:31:07 +0800
Message-ID: <nh4ojto1upd4dr79t4v7bn51rqsp3kc9ft@4ax.com>

On Thu, 28 Jun 2001 13:16:52 +0200, Andreas Necker <Andreas.Necker_at_isb-ag.de> wrote:

>hi,
>
>Erwin Dondorp wrote:
>>
>> 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
>>
>
>or try this
>
>select empno, sal
> from ( select empno, sal
> from emp
> order by sal desc )
> where rownum < 4;
>
>ciao
>andreas

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 - 00:31:07 CDT