Received: from zmail6.easynet.fr (email.easynet.fr [195.114.64.207]) by hub.org (8.9.3/8.9.3) with SMTP id OAA15724 for ; Sun, 19 Dec 1999 14:04:55 -0500 (EST) (envelope-from tesio@easynet.fr) Received: (qmail 91294 invoked from network); 19 Dec 1999 19:04:54 -0000 Received: from mailgate2.easynet.fr (192.168.1.3) by mailserver.easynet.fr with QMQP; 19 Dec 1999 19:04:54 -0000 Received: from pop-nice-206.pops.easynet.fr (HELO atesio) (195.114.95.206) by mrelay2.easynet.fr with SMTP; 19 Dec 1999 19:09:20 -0000 Message-ID: <006201bf4a54$2e9099c0$de5f72c3@atesio> From: "Alain TESIO" To: "Tom Lane" Cc: References: <2946.945622495@sss.pgh.pa.us> Subject: Re: [SQL] group by / having Date: Sun, 19 Dec 1999 19:41:12 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2014.211 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211 > > 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