Skip site navigation (1) Skip section navigation (2)

Re: Re: "Oracle's ROWNUM"

From: Nicolas Ronayette <nronayette(at)alphacsp(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>, Svenne Krap <usenet(at)krap(dot)dk>, pgsql-general(at)postgresql(dot)org
Subject: Re: Re: "Oracle's ROWNUM"
Date: 2001-07-30 14:44:11
Message-ID: 3B6572BB.7060502@alphacsp.com (view raw or flat)
Thread:
Lists: pgsql-general
Tom Lane wrote:

> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
> 
>>Oracle doc says.
>>
> 
>>If you embed the ORDER BY clause in a subquery and place the ROWNUM
>>condition in the top-level query, you can force the ROWNUM condition
>>to be applied after the ordering of the rows. For example, the
>>following query returns the 10 smallest employee numbers. This 
>>is sometimes referred to as a "top-N query": 
>>
> 
>>SELECT * FROM
>>   (SELECT empno FROM emp ORDER BY empno)
>>   WHERE ROWNUM < 11;
>>
> 
> This thing gets more poorly-defined every time I hear about it!?
> 
> Based on what's been said so far, ROWNUM in a WHERE clause means
> something completely different from ROWNUM in the SELECT target list:
> it seems they mean input row count vs output row count, respectively.
> If I do
> 	SELECT rownum, * FROM foo WHERE rownum > 10 and rownum < 20;
> will the output rows be numbered 1 to 9, or 11 to 19?  If I add a
> condition, say "AND field1 < 100", to the WHERE clause, does the rownum
> count include the rows rejected by the additional clause, or not?
> And how do you justify any of these behaviors in a coherent fashion?
> 
> Dare I ask how it behaves in the presence of GROUP BY, HAVING,
> aggregates, DISTINCT, UNION, ... ?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 
> 

here are the results you ask for (from Oracle8i) :

first : select rownum,* make a parse error :-)

SQL> select rownum,* from dba_tables where rownum > 10 and rownum < 20
                    *
ERROR at line 1:
ORA-00936: missing expression


And :

SQL> select rownum,table_name from dba_tables where rownum between  10 
and 20;

no rows selected


SQL> select rownum,table_name from dba_tables where rownum > 10 and 
rownum < 20;

no rows selected

SQL>  select rownum,table_name from dba_tables where rownum > 10;

no rows selected


SQL>  select rownum,table_name from dba_tables where rownum < 20;

     ROWNUM TABLE_NAME
---------- ------------------------------
          1 IND$
          2 FILE$
          3 UNDO$
          4 CLU$
          5 BOOTSTRAP$
          6 ICOL$
          7 FET$
          8 CDEF$
          9 CON$
         10 UET$
         11 TAB$

     ROWNUM TABLE_NAME
---------- ------------------------------
         12 OBJ$
         13 PROXY$
         14 COL$
         15 USER$
         16 TS$
         17 CCOL$
         18 SEG$
         19 UGROUP$

19 rows selected.

SQL> select distinct rownum, table_name  from dba_tables where rownum  < 20;

Same result as above (19 rows)


SQL>  select sum(rownum), tablespace_name from  dba_tables where rownum 
< 20 group by tablespace_name;

SUM(ROWNUM) TABLESPACE_NAME
----------- ------------------------------
         190 SYSTEM



Don't have time for testing  more ...

Hope this will help Postgresql development  :-)



-- 
Nicolas Ronayette +33 (0)6 74 93 67 85
Alphacsp - +33 (0)1 41 37 75 75
--


In response to

Responses

pgsql-general by date

Next:From: Mitch VincentDate: 2001-07-30 14:49:21
Subject: Re:
Previous:From: Marcelo PereiraDate: 2001-07-30 14:31:19
Subject: Re: C / FoxPro

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group