Re: Re: "Oracle's ROWNUM" - Mailing list pgsql-general
From | Nicolas Ronayette |
---|---|
Subject | Re: Re: "Oracle's ROWNUM" |
Date | |
Msg-id | 3B6572BB.7060502@alphacsp.com Whole thread Raw |
In response to | Re: Re: "Oracle's ROWNUM" (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Re: "Oracle's ROWNUM"
|
List | pgsql-general |
Tom Lane wrote: > Hiroshi Inoue <Inoue@tpf.co.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 --
pgsql-general by date: