From: | "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com> |
---|---|
To: | "Jan Wieck" <JanWieck(at)yahoo(dot)com> |
Cc: | "Michael Fuhr" <mike(at)fuhr(dot)org>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Juan Manuel Diaz Lara" <jmdiazlr(at)yahoo(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: How to implement oracle like rownum(function or seudocolumn) |
Date: | 2006-04-08 18:20:21 |
Message-ID: | 36e682920604081120q768ab245je099cdf32c9a332f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 4/8/06, Jan Wieck <JanWieck(at)yahoo(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-04-08 18:52:11 | Re: ERROR: record type has not been registered on CVS head |
Previous Message | Jan Wieck | 2006-04-08 18:02:53 | Re: How to implement oracle like rownum(function or seudocolumn) |