From: Tom Lane <tgl@sss.pgh.pa.us>
To: Julian Scarfe <julian.scarfe@ntlworld.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Ordering with GROUPs
Date: Sun, 18 Aug 2002 15:55:38 -0400
Message-ID: <1115.1029700538@sss.pgh.pa.us> (raw)
In-Reply-To: <046401c246ad$48dbdc10$0500a8c0@Wilbur>
References: <046401c246ad$48dbdc10$0500a8c0@Wilbur>
"Julian Scarfe" <julian.scarfe@ntlworld.com> writes:
> SELECT location.ident, node, count(*) from location, message
> WHERE location.ident = message.ident
> GROUP BY location.ident;
> ERROR: Attribute location.node must be GROUPed or used in an aggregate
> function
> it obviously fails. If node were an integer I could just use an aggregate
> like max() or similar, but it's not, and there's no suitable aggregate for
> point. I can create a trivial one as a work around, but I hope I don't have
> to.
ISTM the problem here is the lack of any ordering operator for POINT,
which defeats GROUP BY, *plus* the lack of any aggregate you might use
for an aggregate-based solution. This is not really a language failing
but a problem with an impoverished datatype. So, if you don't like
Bruno's subselect-based workaround, the dummy aggregate seems the way
to go.
SQL99 contains a whole bunch of verbiage whose intent seems to be that
if you GROUP BY a unique or primary-key column, you can reference the
other columns of that table without aggregation (essentially, the
system treats them as implicitly GROUP BY'd). Sooner or later we'll
probably get around to implementing that, and that would solve your
problem as long as you declare location.ident properly.
regards, tom lane
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: tgl@sss.pgh.pa.us, julian.scarfe@ntlworld.com
Subject: Re: Ordering with GROUPs
In-Reply-To: <1115.1029700538@sss.pgh.pa.us>
* 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