Обсуждение: unexpected 'aggregates not allowed in where clause' error
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
);
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?
"L. Fletcher" <lucasf@vagabond-software.com> writes: > 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 =3D n.note_id > INNER JOIN property.propertyrating pr ON pr.property_rating_id = > =3D 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 =3D = > ps.property_id > WHERE (ps.score_type_id =3D $1 AND p.place_id =3D $2) > OR (ps.score_type_id =3D $3 AND p.place_id =3D $4) > ) > AND pr.created_date >=3D > ( > SELECT MIN(pr.created_date) FROM property.propertyrating pr2 > WHERE pr2.property_rating_id =3D pr.property_rating_id > ORDER BY pr.created_date DESC LIMIT 5 > ); The error message is correct, because the aggregate is associated with the outer query's pr table. Perhaps you meant to write "MIN(pr2.created_date)"? regards, tom lane