Обсуждение: Aggregates not allowed in WHERE clause?

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

> 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?

What about:

SELECT kvvnr FROM lv WHERE semester = (SELECT MAX(semester) FROM lf_sem);

Chris

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

Joachim, 
Try 

SELECT kvvnr, semester FROM lv 
WHERE semester = (SELECT MAX(semester) FROM lf_sem) ; 

Are you sure the MAX function will work on your 
text resp. char/varchar attribute 'semester'? 

Regards, Christoph 

Thanks for all your answers, it is working now.

Christoph Haller <ch@rodos.fzk.de> writes:

> Are you sure the MAX function will work on your 
> text resp. char/varchar attribute 'semester'? 

Yes, this works, I think because the ASCII (or whatever) value of 's'
is lower than that of 'w'.

Greetings,
joachim