Date: Sun, 19 Dec 1999 21:15:48 +0300 (MSK)
From: Oleg Bartunov <oleg@sai.msu.su>
To: Mitch Vincent <mitch@venux.net>
cc: Peter Eisentraut <peter_e@gmx.net>, pgsql-sql@postgreSQL.org
Subject: Re: [SQL] count() question
In-Reply-To: <012301bf4a47$30f59ec0$0300000a@doot.org>
Message-ID: <Pine.GSO.3.96.SK.991219211402.11677F-100000@ra>
Organization: Sternberg Astronomical Institute (Moscow University)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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