Implementing next 30 (or so) rows "sliding window"

From: Allan Kamau <kamauallan(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Implementing next 30 (or so) rows "sliding window"
Date: 2009-12-08 18:49:13
Message-ID: ab1ea6540912081049u35cf88bfna97c733eb8b0806e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I am looking for an efficient way to implement a "sliding window" view
of the data from a query.
I am developing a simple website and would like to provide for
viewing(fetching) only a predetermined maximum number of records per
page.
For example to view 100 records with "30" as the predetermined maximum
number of records to be fetched for any page, will require 3 page
displays with 30 records then a fourth page display having only 10
records.

A probable solution would be to use an order by clause on unique
field(s) in the query (and the LIMIT
<predetermined_max_number_of_records> clause), then store the these
unique field(s) of the first and last records records in the web
application (maybe sent it to the client embedded in the request
response somehow). Then reuse these values for the next query by
placing them in the where clause with a greater than or less than
comparison operator (use the or operator in the case where of a
composite unique key). Then use the order by and limit as usual.
The above solution may be limited to only subsequent page views (prev
or next) but will not work for page skips.

Allan.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message jackassplus 2009-12-08 18:50:54 Counts and percentages and such
Previous Message Rob W 2009-12-08 18:39:35 Re: Query using partitioned table hangs