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 22:03:02 +0000
Message-ID: <1116367382.9362.11.camel@localhost.localdomain> (raw)
In-Reply-To: <428A3B54.4070902@pobox.com>
References: <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>
<20050517170636.GC12405@phlogiston.dyndns.org>
<428A2E81.2060501@pobox.com>
<1116353539.26520.5.camel@localhost.localdomain>
<428A3B54.4070902@pobox.com>
On Tue, 2005-05-17 at 15:43 -0300, Alain wrote:
> [how to solve the get next 100 records problem]
I am assuming this is for a web like interface, in other words that
cursors are not applicable
> > [me]
> > 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)
>
> I tried that. It does not work in the generic case: 6 MegaRec, telephone
> listing, alphabetical order. The problem is that somewhere there is a
> single user with too many entries (over 1000). I even tried to filter
> the repetitions, but somewhere I get stuck if one guy has too mny
> entries (one for each phone number).
>
> I tried using both the name and the primary key (with a combined index),
> to get faster to the record I want, but I was not sucessfull in building
> a where clause.
lets say pkey is your primary key and skey is your sort key, and
there exists an index on (skey,pkey)
your first select is
select ... from tab ORDER by skey,pkey LIMIT 100;
your subsequent selects are
select ... from tab WHERE skey>skey_last
OR (skey=skey_last AND pkey>pkey_last)
ORDER BY skey,pkey
LIMIT 100 OFFSET 100;
> I would appreciate any help, in fact this is my primary reason for
> joining this list ;-)
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: <1116367382.9362.11.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