Re: [SQL] group by / having

"Alain TESIO" <tesio(at)easynet(dot)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