From: Ragnar Hafstað <gnari@simnet.is>
To: Alain <alainm@pobox.com>
Cc: Sql-Postgre <pgsql-sql@postgresql.org>
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
Date: Tue, 17 May 2005 18:12:19 +0000
Message-ID: <1116353539.26520.5.camel@localhost.localdomain> (raw)
In-Reply-To: <428A2E81.2060501@pobox.com>
References: <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>
<20050517170636.GC12405@phlogiston.dyndns.org>
<428A2E81.2060501@pobox.com>
On Tue, 2005-05-17 at 14:48 -0300, Alain wrote:
>
> Andrew Sullivan escreveu:
> > On Thu, May 12, 2005 at 01:07:00PM -0600, Dennis.Jiang@thomson.com wrote:
> >
> >>Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
> >>so, we can write the following query:
> >
> >
> > No. What is the purpose of your query? You could use ORDER BY and
> > LIMIT..OFFSET to do what you want. I think.
>
> The problem is probably speed. I have done a lot of tests, and when
> OFFSET gets to a few thousands on a multimega-recs database, it gets
> very very slow...
is there not a similar loss of speed using ROWNUM on oracle?
> ... Is there any other to work around that?
if you are ordering by a unique key, you can use the key value
in a WHERE clause.
select ... where ukey>? order by ukey limit 100 offset 100;
(the ? is placeholder for the last value of ukey returned
from previous select)
gnari
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gnari@simnet.is, alainm@pobox.com
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
In-Reply-To: <1116353539.26520.5.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox