From: | "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com> |
---|---|
To: | <database(at)gurubase(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: select question |
Date: | 2000-07-29 20:34:53 |
Message-ID: | 00072916460206.17801@comptechnews |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, 29 Jul 2000, database(at)gurubase(dot)com wrote:
> Dear all,
>
> Is there a way I can select the top 50 rows from table, 51 - 100 rows from
> table etc.... (with order clause)? It is because I am writing a message board
> and I would like to create the prev/next button on different page.
>
> Many thanks.
>
> Best regards,
> Boris
A cursor might also work for you.
Example:
$offset = $pageno * $rowsperpage;
BEGIN;
DECLARE mycur CURSOR FOR SELECT * FROM mytable WHERE age > 20 ORDER BY name;
FETCH FORWARD $offset FROM mycur;
CLOSE mycur;
END;
I forget what the advantages/disadvantages are between CURSOR and LIMIT. I've
used a CURSOR and it works fine for doing paging. One thing I'd still like to
know, is what are the most efficient ways to get the count of rows in cursor? I
guess a SELECT count(*) is the only way but seems that would be slow on large
tables. Hmm, maybe SELECT INTO TEMPORARY TABLE with LIMIT is a good way,
then you can do a SELECT count(*) on the temp table without scanning the whole
larger table again. Anyone reading this having any comments on this?
--
- Robert
From | Date | Subject | |
---|---|---|---|
Next Message | dave | 2000-07-29 21:41:39 | problem compiling php for pgsql |
Previous Message | Lamar Owen | 2000-07-29 19:51:51 | Re: problem compiling php for pgsql |