Re: Aggregate in Correlated SubQuery

On 2006-10-15, Niederland <niederland(at)gmail(dot)com> wrote:
> Before postgresql 8.1.5, I could do the following to find the first
> lead that created a prospect in my application.
>
> SELECT
> Lead.LeadID,
> Prospect.ProspectID
> FROM
> Prospect INNER JOIN Lead USING (ProspectID)
> WHERE
> Lead.CreationDate = (SELECT MIN(Lead.CreationDate) FROM Lead AS LL
> WHERE LL.ProspectID = Lead.ProspectID)

I think that was always wrong, and that what you wanted was
MIN(LL.CreationDate).

The aggregate in the subquery must be an aggregate over the subquery's
rows, not over the outer query.

(Though there are ways to do this query without the subquery at all)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services