Re: select count(*) and limit

On 5/18/06 6:51 AM, "Verena Ruff" <lists(at)triosolutions(dot)at> wrote:

> Hi,
>
> sometimes I have queries with a LIMIT statement. Now I'd like to present
> the user the returned records and inform him how many records there are
> if there was no LIMIT statement. Is it possible to get all neccessary
> information with one query?
> This works:
> SELECT * FROM table LIMIT 20
> SELECT count(*) FROM table
> But is it possible to have one query returning both, the records and the
> count?

Verena

I think the answer is "no", not when using "LIMIT". However, For the count
part, a trick to speed things up is to use the output from EXPLAIN to
approximate the number of rows. If the table has been vacuumed on a regular
basis, the results are often pretty close to those returned by count(*). An
alternative to using the LIMIT clause is to use a cursor, but the ability to
do so depends on the environment in which you are working. In a web
environment, cursors are not useful given the stateless nature of the web
interface. Cursors are explained in the Docs.

Sean