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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
--
From | Date | Subject | |
---|---|---|---|
Next Message | Mitch Vincent | 2001-07-30 14:49:21 | Re: |
Previous Message | Marcelo Pereira | 2001-07-30 14:31:19 | Re: C / FoxPro |