Thread: group by / having

Hello,

Do you know why this command doesn't work ?

> select * from T;
x|y
-+-
1|1
1|2
2|1
2|2
2|3

> select X,Y from T group by X having Y=min(Y);

ERROR:  Illegal use of aggregates or non-group column in target list


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 query "select X,Y from T group by X" works under MySQL and
returns exactly what I want, how can I do it in PostgreSQL ?

Thanks for your help,
Alain

"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 likeAttribute 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

> > 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

This solves my problem but I'm surprised that having doesn't behave as
I expected. Let's take an example :

X Y Z
1 1 A
1 2 B
2 1 C
3 1 D
3 2 E
3 3 F

I want one line for each X value where the Y value is minimal, and I want
to get the T column also.

Isn't this a part of the SQL standard ?

select X,Y,Z from T group by X having Z=min(Z)

Any warkaround in postgresql ?

Regards,
Alain