Re: Re: "Oracle's ROWNUM"

From: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
To: Svenne Krap <usenet(at)krap(dot)dk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Re: "Oracle's ROWNUM"
Date: 2001-07-30 07:19:20
Message-ID: 3B650A78.1926B0C5@tpf.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Svenne Krap wrote:
>
> On Mon, 30 Jul 2001 00:05:42 +0000 (UTC), tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane)
> wrote:
>
> >Svenne Krap <usenet(at)krap(dot)dk> writes:
> >> I thought of the possibility to do something like
> >
> >> select rownum as artistplacement, s.* from (select rownum as
> >> techplacement, * from ranking order by technical_points) order by
> >> s.artiste_points
> >
> >This is an interesting example, but I do not believe that ROWNUM could
> >really be used that way. You are assuming that ROWNUM is computed after
> >the rows are sorted --- but in fact SQL requires the target expressions
> >of a SELECT to be computed before ORDER BY is applied. What would you
> >expect to happen with
> > SELECT rownum, * FROM table ORDER BY 1
> >
> >Does anyone know what the actual semantics of Oracle's ROWNUM are?
>
> Well, I'm quite positive, that Oracle calculates ROWNUM after having
> ordered the set. As I'm not sure, ROWNUM is counted as part of the
> resultset (until it is selected of a second select)
>

Isn't it a little different ?
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;

regards,
Hiroshi Inoue

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dave Page 2001-07-30 07:19:38 RE: [ODBC] Visual Basic and PostgreSQL ODBC
Previous Message Kovács Péter 2001-07-30 07:12:04 RE: Large Objects