"L. Fletcher" <lucasf@xxxxxxxxxxxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
