Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id E6576476144
	for <pgsql-sql@postgresql.org>; Wed, 19 Jun 2002 04:05:14 -0400 (EDT)
Received: from waffel.germanistik.uni-bonn.de (waffel.germanistik.uni-bonn.de
	[131.220.90.150])
	by postgresql.org (Postfix) with ESMTP id 5683A475BC0
	for <pgsql-sql@postgresql.org>; Wed, 19 Jun 2002 04:05:12 -0400 (EDT)
Received: by waffel.germanistik.uni-bonn.de (Postfix, from userid 1000)
	id 689D213198E; Wed, 19 Jun 2002 10:04:24 +0200 (CEST)
To: pgsql-sql@postgresql.org
Subject: Aggregates not allowed in WHERE clause?
From: Joachim Trinkwitz <jtr@uni-bonn.de>
Date: 19 Jun 2002 10:04:24 +0200
Message-ID: <874rfzr8xj.fsf@waffel.germanistik.uni-bonn.de>
Lines: 47
User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.4 (Honest Recruiter)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Archive-Number: 200206/240
X-Sequence-Number: 8205

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?

Clueless,
joachim