From: | "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Svenne Krap" <usenet(at)krap(dot)dk>, <pgsql-general(at)postgresql(dot)org> |
Subject: | RE: Re: "Oracle's ROWNUM" |
Date: | 2001-07-30 21:49:10 |
Message-ID: | EKEJJICOHDIEMGPNIFIJKEHFFAAA.Inoue@tpf.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> 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.
They mean output row count AFAIK.
> 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?
No rows are returned because rownum 2 doesn't exist without rownum 1
and so on.
> 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?
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, ... ?
>
I don't know the details about it unfortunately.
regards,
Hiroshi Inoue
From | Date | Subject | |
---|---|---|---|
Next Message | Lee Harr | 2001-07-30 22:25:11 | Re: referential integrity violation |
Previous Message | newsreader | 2001-07-30 21:34:53 | Re: database information |