select question

* 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

-----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-----

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

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:

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/
______________________________________________________