Re: [SQL] group by / having - Mailing list pgsql-sql

From Alain TESIO
Subject Re: [SQL] group by / having
Date
Msg-id 006201bf4a54$2e9099c0$de5f72c3@atesio
Whole thread Raw
In response to Re: [SQL] group by / having  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
> > 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




pgsql-sql by date:

Previous
From: "Mitch Vincent"
Date:
Subject: New count() question
Next
From: Timothy Laswell
Date:
Subject: Re: [GENERAL] NOTICE: (transaction aborted): queries ignored until END