Re: [SQL] group by / having
От | Alain TESIO |
---|---|
Тема | Re: [SQL] group by / having |
Дата | |
Msg-id | 006201bf4a54$2e9099c0$de5f72c3@atesio обсуждение исходный текст |
Ответ на | Re: [SQL] group by / having (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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 по дате отправления: