Re: Yet Another COUNT(*)...WHERE...question

On 16/08/07, Rainer Bauer <usenet(at)munnin(dot)com> wrote:
> Gregory Stark wrote:
>
> >"Rainer Bauer" <usenet(at)munnin(dot)com> writes:
> >
> >> Anyway, what Phoenix is trying to say is that 2 queries are required: One to
> >> get the total count and one to get the tuples for the current page. I reckon
> >> it would help, if the query returning the result set could also report the
> >> total no. of tuples found. Somthing like
> >> SELECT COUNT(*), * FROM <table> WHERE <cond> OFFSET <o> LIMIT <l>
> >>
> >> Or is there a way to do that?
> >
> >Well anything like the above would just report l as the count.
>
> True, but what about this:
>
> SELECT (SELECT COUNT(*) FROM <table> WHERE <cond>), * FROM <table> WHERE <cond> OFFSET <o> LIMIT <l>
>

Whoa, this may not please SQL puritans but I love it! And yes, it is
cached. I find the idea of temporary tables and storing counts for
different 'slices' of my data untenable with all the complex mishmash
of triggers and such. The count(*) query seems to take a bit in the
beginning but works ok thereafter because it seems to be auto-cached.
Sweet. Thanks for sharing!!