Re: "Oracle's ROWNUM"

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)

In oracle btw. you use it for a construct like the following (not
having LIMIT)

select s.* from (select rownum, t.* from (select rownum,* from table
where expressions order by sortcolumn) t where t.rownum < max_row) s
where rownum > min_row

And btw what actually happen to the data of a query if you do "order
by 1" ???

Svenne
--
Mail usenet(at)krap(dot)dk - svenne(at)krap(dot)dk - PGP key id : 0xDF484022
ICQ: 5434480 - http://www.krap.dk - http://www.krap.net
PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022