From: cmccormick@mailsnare.net
To: pgsql-sql@postgresql.org
Importance: Normal
Sensitivity: Normal
Message-ID: <W17018311537171040417606@mail.mailsnare.net>
Date: Fri, 20 Dec 2002 20:53:26 +0000
Subject: Need help paging through record sets
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Hello everyone, and thanks for reading my first newbie post. :-)

I am a neopyhte PHP and postgreSQL user, with a website at www.the-athenaeu=
m.org.  We store (among other things) artworks, which people can view in a =
list, sorted by artist name, date, medium, etc.

We now have enough works that I need to rewrite the PHP listings script (an=
d its embedded SQL) so that users can page through records.  As an example,=
 if a user is looking at works by date ascending, they may want to see 100 =
records at a time.  Since we have 600+ records, there would be 7 pages.  Th=
ey'd start on the first page (of course!) and there would be links to pages=
 2 through 7 as well, just like with results pages of a Google search.  The=
y could, from page 1, click any of the other pages to go immdiately to that=
 set of 100 records for display.

I see this kind of thing all over the place, and in looking it up, I see mo=
st solutions use "SELECT TOP x", which postgreSQL doesn't seem to have.  I =
know how to use LIMIT, but that always starts from the top.  I could add a =
piece to the WHERE clause, say something like "WHERE date > 01-02-1853", bu=
t how do I know where the cutoffs are several pages along, without retrievi=
ng the whole record set?

I suppose the optimal solution for me would be to sort all of the records, =
then be able to select a range from that sorted record set.  So, if they cl=
ick the link to page 3, I'd like to do this (in pseudocode):

1.  SORT records by the date field, descending
2.  Retrieve only records 200-299 from the sorted list

Is there a way to do that?  How is it done elsewhere?

Thanks in advance for your help,
Chris McCormick, webmaster
The Athenaeum - Interactive Humanities Online
www.the-athenaeum.org