Aggregates not allowed in WHERE clause?

Поиск
Список
Период
Сортировка
От Joachim Trinkwitz
Тема Aggregates not allowed in WHERE clause?
Дата
Msg-id 874rfzr8xj.fsf@waffel.germanistik.uni-bonn.de
обсуждение исходный текст
Ответы Re: Aggregates not allowed in WHERE clause?  ("Christopher Kings-Lynne" <chriskl@familyhealth.com.au>)
Re: Aggregates not allowed in WHERE clause?  (Achilleus Mantzios <achill@matrix.gatewaynet.com>)
Re: Aggregates not allowed in WHERE clause?  (Christoph Haller <ch@rodos.fzk.de>)
Список pgsql-sql
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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Achilleus Mantzios
Дата:
Сообщение: Re: text vs varchar
Следующее
От: "Christopher Kings-Lynne"
Дата:
Сообщение: Re: Aggregates not allowed in WHERE clause?