Re: select question

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