Thread: Implementing next 30 (or so) rows "sliding window"
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.
Hi,
I did follow the basic advise and consulted the documentation for
"SELECT" and came across "[ FETCH { FIRST | NEXT } [ count ] { ROW |
ROWS } ONLY]" clause which seems to satisfy my requirement.
Allan.
On Tue, Dec 8, 2009 at 9:49 PM, Allan Kamau <kamauallan@gmail.com> wrote:
> 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.
>
On Tue, Dec 8, 2009 at 2:12 PM, Allan Kamau <kamauallan@gmail.com> wrote:
> Hi,
> I did follow the basic advise and consulted the documentation for
> "SELECT" and came across "[ FETCH { FIRST | NEXT } [ count ] { ROW |
> ROWS } ONLY]" clause which seems to satisfy my requirement.
>
that's basically LIMIT, you have to combine that with OFFSET
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
On Tue, Dec 8, 2009 at 11:42 AM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > that's basically LIMIT, you have to combine that with OFFSET Keep in mind that offset begins to preform badly for large values. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Tue, Dec 8, 2009 at 12:12 PM, Allan Kamau <kamauallan@gmail.com> wrote:
> Hi,
> I did follow the basic advise and consulted the documentation for
> "SELECT" and came across "[ FETCH { FIRST | NEXT } [ count ] { ROW |
> ROWS } ONLY]" clause which seems to satisfy my requirement.
This is a cursor, which is one of two very basic ways of doing this.
IF you have presistent objects in your app layer, you can declare a
cursor and reuse it as your user moves through the list, one page
after another. If you do not have some kind of persistence layer for
db objects, then declaring a cursor each time a user navigates to
another page won't really gain you much, and adds complexity.
In that instance offset / limit work fairly well. As another poster
mentions, it gets slow with large offsets. However, most of the time,
like in forum software, you don't go dragging to page 258 of a result
set very often, so a small delay when doing so is usually acceptable.
On Tue, Dec 8, 2009 at 3:51 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Dec 8, 2009 at 12:12 PM, Allan Kamau <kamauallan@gmail.com> wrote:
>> Hi,
>> I did follow the basic advise and consulted the documentation for
>> "SELECT" and came across "[ FETCH { FIRST | NEXT } [ count ] { ROW |
>> ROWS } ONLY]" clause which seems to satisfy my requirement.
>
> This is a cursor,
no. this is sql 2008 syntax for the LIMIT clause:
http://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-LIMIT
> which is one of two very basic ways of doing this.
i agree, that using cursor is another (preferred?) solution for this,
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157