Thread: select question
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
* database@gurubase.com <database@gurubase.com> [000729 10:57] 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. I think you want to look at the OFFSET and LIMIT clauses explained in the documentation. -Alfred
On Sat, 29 Jul 2000, database@gurubase.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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > 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. Look at the documentation for the 'limit' clause. Ian -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.1 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE5gyXxfn9ub9ZE1xoRAiIRAKCF4CCP3CGVVl+aY4jmdP+def2JYQCfRg8e zWP3OaPFXxr34n8FMSV4N4A= =33xl -----END PGP SIGNATURE-----
Use the limit clause.
SELECT message_text FROM messages ORDER BY creation_date LIMIT $limit,
$offset.
LIMIT 10, 0 gets you the first batch.
LIMIT 10, 10 gets you the second batch.
LIMIT 10, 20 gets you the third, etc.
-----------------------------------------
Water overcomes the stone;
Without substance it requires no opening;
This is the benefit of taking no action.
Lao-Tse
Brian Knox
Senior Systems Engineer
brian@govshops.com
On Sun, 30 Jul 2000 database@gurubase.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
>
g <brian@wuwei.govshops.com> writes:
> Use the limit clause.
> SELECT message_text FROM messages ORDER BY creation_date LIMIT $limit,
> $offset.
> LIMIT 10, 0 gets you the first batch.
> LIMIT 10, 10 gets you the second batch.
> LIMIT 10, 20 gets you the third, etc.
BTW, a little tip that a number of people have gotten burnt by not
knowing: when you do this you *must* use an ORDER BY clause that's
strong enough to order the result rows completely. Otherwise you
are asking for slices out of an undefined ordering of the rows.
You could get a different ordering on each request, leading to
inconsistent slices --- in other words, missing or repeated rows.
This does actually happen in Postgres 7.0, because the planner
optimizes queries with small limit+offset differently from those
without.
regards, tom lane
Tom Lane writes: > g <brian@wuwei.govshops.com> writes: > > Use the limit clause. > > SELECT message_text FROM messages ORDER BY creation_date LIMIT $limit, > > $offset. > > > LIMIT 10, 0 gets you the first batch. > > LIMIT 10, 10 gets you the second batch. > > LIMIT 10, 20 gets you the third, etc. > > BTW, a little tip that a number of people have gotten burnt by not > knowing: when you do this you *must* use an ORDER BY clause that's > strong enough to order the result rows completely. Otherwise you > are asking for slices out of an undefined ordering of the rows. > You could get a different ordering on each request, leading to > inconsistent slices --- in other words, missing or repeated rows. > > This does actually happen in Postgres 7.0, because the planner > optimizes queries with small limit+offset differently from those > without. > > regards, tom lane Hi, I wonder if one must activate the LIMIT clause somewhere, bacause for me it does nothing. I'm using postgresql Version: 7.0.2 in a Debina potato system. Thanx. -- ______________________________________________________ Felipe Alvarez Harnecker. QlSoftware. Tel. 09.874.60.17 e-mail: felipe.alvarez@qlsoft.cl Potenciado por Debian GNU/Linux http://www.qlsoft.cl/ ______________________________________________________
Felipe Alvarez Harnecker <felipe@qlsoft.cl> writes:
> Hi, I wonder if one must activate the LIMIT clause somewhere,
uh ... no ...
> bacause for me it does nothing.
Details? What query did you issue exactly, and what did you get?
regards, tom lane
Tom Lane writes: > Felipe Alvarez Harnecker <felipe@qlsoft.cl> writes: > > Hi, I wonder if one must activate the LIMIT clause somewhere, > > uh ... no ... > > > bacause for me it does nothing. > > Details? What query did you issue exactly, and what did you get? > > regards, tom lane > Hi, before borring you, i've tested the query with psql and it worked. I was testing the query with pgaccess. Maybe i'ts a library bug or something. Regards. -- ______________________________________________________ Felipe Alvarez Harnecker. QlSoftware. Tel. 09.874.60.17 e-mail: felipe.alvarez@qlsoft.cl Potenciado por Debian GNU/Linux http://www.qlsoft.cl/ ______________________________________________________