From: | "Alain TESIO" <tesio(at)easynet(dot)fr> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-sql(at)postgreSQL(dot)org> |
Subject: | Re: [SQL] group by / having |
Date: | 1999-12-19 18:41:12 |
Message-ID: | 006201bf4a54$2e9099c0$de5f72c3@atesio |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Alain TESIO | 1999-12-19 19:06:38 | Right to create temp tables |
Previous Message | Mitch Vincent | 1999-12-19 18:26:59 | New count() question |