To: Tomasz Myrta <jasiek@klaster.net>
Cc: Andras Kutrovics <n-drew@freemail.hu>, pgsql-sql@postgresql.org
Subject: Re: Simple SQL Question
References: <418BB051.6070808@freemail.hu> <418BC1F3.7080205@klaster.net>
In-Reply-To: <418BC1F3.7080205@klaster.net>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 06 Nov 2004 03:17:43 -0500
Message-ID: <87zn1vl7bs.fsf@stark.xeocode.com>
Lines: 34
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


Tomasz Myrta <jasiek@klaster.net> writes:

> > select * from table1 LIMIT x
> > gives me the first x row of the result.
> > After that, I save the last value, and next time, I adjust
> > the query as
> > select * from table1 where itemkey>:lastvalue LIMIT x
> 
> Why do you complicate it so much? Everything you need is:
> 
> select * from table1 LIMIT x
> select * from table1 LIMIT x OFFSET x
> select * from table1 LIMIT x OFFSET 2*x
> 
> Remember to sort rows before using limit/offset.

There are two good reasons to prefer his Andras' solution to yours.

a) If the data is modified between the two queries his will continue from
where the previous page left off. Yours will either skip records or overlap
with the previous page.

b) If itemkey is indexed his will be an efficient index scan that performs
similarly regardless of what page is being fetched. Yours will perform more
and more slowly as the user gets deeper into the results.


Note that both queries are wrong however. You need an "ORDER BY itemkey" or
else nothing guarantees the second page has any relation at all to the first
page.

-- 
greg