Re: Ordering with GROUPs

"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