Message-ID: <012301bf4a47$30f59ec0$0300000a@doot.org>
From: "Mitch Vincent" <mitch@venux.net>
To: "Peter Eisentraut" <peter_e@gmx.net>
Cc: <pgsql-sql@postgreSQL.org>
References: <Pine.LNX.4.21.9912181655230.356-100000@localhost.localdomain>
Subject: Re: [SQL] count() question
Date: Sun, 19 Dec 1999 12:33:40 -0500
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

> 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