Re: select count(*) and limit

Sean Davis schrieb:
>
> 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(*).
My chosen example was to simple, sorry for that. The real query isn't
just from one table, it contains a few joins, so I guess this trick
won't work here.

> 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.
>
The queries are for a webpage, so coursers won't be usefull.

Regards,
Verena