Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 228F447684A for ; Wed, 19 Jun 2002 04:16:48 -0400 (EDT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 83B9F47678A for ; Wed, 19 Jun 2002 04:15:51 -0400 (EDT) Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.11.6/8.11.6) with ESMTP id g5J8IDq07194; Wed, 19 Jun 2002 11:18:13 +0300 Date: Wed, 19 Jun 2002 11:18:13 +0300 (EEST) From: Achilleus Mantzios To: Joachim Trinkwitz Cc: pgsql-sql@postgresql.org Subject: Re: Aggregates not allowed in WHERE clause? In-Reply-To: <874rfzr8xj.fsf@waffel.germanistik.uni-bonn.de> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200206/242 X-Sequence-Number: 8207 On 19 Jun 2002, Joachim Trinkwitz wrote: > Hi all, > > I have a table (lv) with a field "semester" and I'm trying to fish out all > rows which have a value corresponding to a max() value of another > table's (lf_sem) "semester" field. The intention is to keep a value > with the current term in lf_sem so I can get all rows which concern > this term. > > Example tables: > > lv > == > semester | kvvnr > ---------+------ > 2001ss | 4185 > 2001ss | 4203 > 2002ws | 4163 > 2002ws | 4190 > > lf_sem > ====== > semester > -------- > 2001ws > 2002ss > 2002ws > > At first I tried this query: > > SELECT kvvnr > FROM lv, lf_sem > WHERE lv.semester = max(lf_sem.semester); > > This yields the message: 'Aggregates not allowed in WHERE clause'. > > Next I tried this one: > > SELECT kvvnr, max(lf_sem.semester) AS akt_semester > FROM lv, lf_sem > WHERE lv.semester = akt_semester; > > Now I got: 'Attribute 'akt_semester' not found' > > Is there another way to get what I want? SELECT lv.kvvnr,lv.semester from lv where lv.semester = (select max(semester) from lf_sem); > > Clueless, > joachim > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr