I recall Jan wrote about why it's impossible to get count() with
LIMIT clause. Check hackers mail-archive. But I do need also such a
feature.
regards, Oleg
On Sun, 19 Dec 1999, Mitch Vincent wrote:
> Date: Sun, 19 Dec 1999 12:33:40 -0500
> From: Mitch Vincent <mitch@venux.net>
> To: Peter Eisentraut <peter_e@gmx.net>
> Cc: pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] count() question
>
> > Why do you want to avoid that? If you need two different results then you
> > probably have to use two different queries.
>
> Speed, resource consumption and there might not be any need for it :-)
>
> >I assume you need the count to
> > display something like "x matches found" and use LIMIT/OFFSET to step
> > through them page by page.
>
> I do use LIMIT / OFFSET and that's part of the problem. I can't get a total
> count from a query when I use LIMIT and OFFSET (at least I don't know how)..
>
> > In that case there is really no way but to
> > query twice. If your query is really complicated and slow you might want
> > to put the complete query results in a temporary table, and select the
> > count and the to-be-displayed data from there.
>
> Indeed, that might be a better way to structure the search however at this
> point I have to work withion what is already there (there is a huge
> application built around the search engine). I am going to totally re-write
> this but can't do that now, now I needed to add some functionality with a
> minimum impact on the rest of the application.
>
> If I have to do another query, so be it. I just wanted to make sure there
> wasn't a way for me to do something like :
>
> select * from applicants as a, count(*) as total where a.status = 'A' limit
> 10 offset 0
>
> Of course I know I can't do that like that but thought there might be a
> substitute or alternative way of getting the count from within the query.
>
> -Mitch
>
>
>
>
>
>
> ************
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83