pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alain TESIO <tesio@easynet.fr>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] group by / having
Date: Sun, 19 Dec 1999 11:54:55 -0500
Message-ID: <2946.945622495@sss.pgh.pa.us> (raw)
In-Reply-To: <00cb01bf4a3a$c6046a80$f15f72c3@atesio>

"Alain TESIO" <tesio@easynet.fr> writes:
> Do you know why this command doesn't work ?
> 	select X,Y from T group by X having Y=min(Y);
> ERROR:  Illegal use of aggregates or non-group column in target list

You can't do that because the HAVING condition is evaluated *after*
aggregation of the rows with the same X into a single row.  (As it must
be, in order to refer to the min() result over those input rows.)
So, there's no unique Y to use.  Similarly, you can't refer directly
to Y in the SELECT target list.  However, you can refer to an aggregate
function computed on Y.

> My goal is quite simple : get only one line per X value (the value which is
> returned for Y is not important as long as it's one of the values linked to
> the right X).

The usual solution is simply

	select X,min(Y) from T group by X

Of course max(Y) would work as well given the rules you set.

> The query "select X,Y from T group by X" works under MySQL and
> returns exactly what I want,

Then MySQL is broken.  That query is invalid according to the SQL
standard, because there isn't any well-defined way to choose which Y
value to return.  To get a result that is not implementation-dependent,
you must add some additional constraint on which Y you want --- that's
what writing min() or max() does for you.

BTW, I agree Postgres' error message is not very helpful.  The next
release will say something like
	Attribute T.Y must be GROUPed or used in an aggregate function
If anyone's got suggestions for the best wording, let me know.

			regards, tom lane



view thread (4+ messages)  latest in thread

Message-ID: <2946.945622495@sss.pgh.pa.us>
Permalink:  ../2946.945622495@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2946.945622495@sss.pgh.pa.us

 · 

reply

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, tesio@easynet.fr
  Subject: Re: [SQL] group by / having
  In-Reply-To: <2946.945622495@sss.pgh.pa.us>
  References: <00cb01bf4a3a$c6046a80$f15f72c3@atesio> <2946.945622495@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