Re: How to implement oracle like rownum(function or seudocolumn)

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