From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> |
Cc: | Svenne Krap <usenet(at)krap(dot)dk>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Re: "Oracle's ROWNUM" |
Date: | 2001-07-30 14:13:02 |
Message-ID: | 20636.996502382@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | tony | 2001-07-30 14:23:24 | referential integrity violation |
Previous Message | Trond Eivind Glomsrød | 2001-07-30 13:09:27 | Re: readline and rh7.1 |