Re: [SQL] group by

"tjk(at)tksoft(dot)com" <tjk(at)tksoft(dot)com> writes:
> Is there something amiss with the "GROUP BY" clause?

No. Not in this example, anyway.

> E.g. "select email,username from emails group by email"
> produces
> "Illegal use of aggregates or non-group column in target list"

As it should. Which username are you expecting to retrieve from
each group? The query is not well-defined. You could do something
like

select email,min(username) from emails group by email

and get a well-defined result. Basically, if you use GROUP BY
then any non-grouped columns can only appear as the arguments of
aggregate functions.

Postgres versions before 6.5 were rather lax about catching this
class of error, but 6.5 is more careful about it.

I'm not very happy with the wording of the error message; it's both
vague and confusing. But I'm not sure how to do better, either.
The routine that checks for this is looking at a very low-level
condition that could cover a multitude of sins, and I don't see any
easy way to deliver a more-specific error message that wouldn't be
wrong (or at least misleading) as often as not :-(

> ... and so does
> "select distinct on email email,username from emails"

SELECT DISTINCT ON is a non-standard abomination that does not
give predictable results. It's basically the same problem: if
you take only one tuple out of each group with the same value
of "email", which tuple do you get? The results are totally
dependent on unspecified implementation choices. I've suggested
several times that we ought to remove SELECT DISTINCT ON, and unless
there's a real groundswell of discontent I'm probably going to
rip it out of 7.0...

regards, tom lane