Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <gnari@simnet.is>
To: Alain <alainm@pobox.com>
Cc: Sql-Postgre <pgsql-sql@postgresql.org>
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>
Content-Type: text/plain
Date: Tue, 17 May 2005 22:03:02 +0000
Message-Id: <1116367382.9362.11.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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