On 4/8/06, Jan Wieck <JanWieck@yahoo.com> wrote:
> Someone correct me if I'm wrong, but I was allways under the impression
> that Oracle's ROWNUM is a thing attached to a row in the final result
> set, whatever (possibly random) order that happens to have. Now a) this
> is something that IMHO belongs into the client or stored procedure code,
> b) if I am right, the code below will break as soon as an ORDER BY is
> added to the query and most importantly c) if a) cannot do the job, it
> indicates that the database schema or business process definition lacks
> some key/referential definition and is in need of a fix.
Yes, a rownum is assigned at fetch time.
An example is the following PostgreSQL query:
SELECT id FROM sometable ORDER BY id LIMIT 5;
In Oracle-land is written as:
SELECT id FROM (SELECT id FROM sometable ORDER BY id) WHERE rownum <= 5;
> My humble guess is that c) is also the reason why the ANSI didn't find a
> ROWNUM desirable.
I believe this is a good assumption.
--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324