From: Oleg Bartunov <oleg@sai.msu.su>
To: Mitch Vincent <mitch@venux.net>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] count() question
Date: Sun, 19 Dec 1999 21:15:48 +0300 (MSK)
Message-ID: <Pine.GSO.3.96.SK.991219211402.11677F-100000@ra> (raw)
In-Reply-To: <012301bf4a47$30f59ec0$0300000a@doot.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oleg@sai.msu.su, mitch@venux.net, peter_e@gmx.net
Subject: Re: [SQL] count() question
In-Reply-To: <Pine.GSO.3.96.SK.991219211402.11677F-100000@ra>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox