| 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: | Whole Thread | Raw Message | 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?
| 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 |