From: Tom Lane <tgl@sss.pgh.pa.us>
To: tjk@tksoft.com <tjk@tksoft.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] group by
Date: Sun, 05 Dec 1999 00:47:00 -0500
Message-ID: <6008.944372820@sss.pgh.pa.us> (raw)
In-Reply-To: <199912050349.TAA01753@uno.tksoft.com>
"tjk@tksoft.com" <tjk@tksoft.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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, tjk@tksoft.com
Subject: Re: [SQL] group by
In-Reply-To: <6008.944372820@sss.pgh.pa.us>
References: <199912050349.TAA01753@uno.tksoft.com> <6008.944372820@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