Re: Aggregates not allowed in WHERE clause?

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