On 16/08/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > Gregory Stark wrote: > > >"Rainer Bauer" <[EMAIL PROTECTED]> 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!! ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
