unexpected 'aggregates not allowed in where clause' error

From: "L(dot) Fletcher" <lucasf(at)vagabond-software(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: unexpected 'aggregates not allowed in where clause' error
Date: 2005-03-03 18:19:59
Message-ID: 01f201c5201d$9c4e1330$a001a8c0@sf.vagabond.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I am getting a strange "aggregates not allowed in where clause" error when I try to execute the below query:

SELECT n.note, pr.property_id AS PropertyID
FROM property.note n
INNER JOIN property.rating_note rn ON rn.note_id = n.note_id
INNER JOIN property.propertyrating pr ON pr.property_rating_id = rn.property_rating_id
WHERE pr.property_id IN
(
SELECT p.property_id
FROM property.vw_property_summary p
INNER JOIN property.propertyscore ps ON p.property_id = ps.property_id
WHERE (ps.score_type_id = $1 AND p.place_id = $2)
OR (ps.score_type_id = $3 AND p.place_id = $4)
)
AND pr.created_date >=
(
SELECT MIN(pr.created_date) FROM property.propertyrating pr2
WHERE pr2.property_rating_id = pr.property_rating_id
ORDER BY pr.created_date DESC LIMIT 5
);

Please note that the aggregate is within a subquery of the where clause, and not in the where clause proper. Is this error message a bug or am I doing something wrong?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Stark 2005-03-03 18:37:36 Re: Compatible Dumps
Previous Message Frank Finner 2005-03-03 18:13:46 Re: PL/Perl trusted throws error on example function