To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: using the aggregate function max()
Date: Fri, 23 Sep 2011 08:37:05 +0200
Lines: 29
Message-ID: <j5h9da$f1q$1@dough.gmane.org>
References: <201109221949.20671.johnf@jfcomputer.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: <201109221949.20671.johnf@jfcomputer.com>

John Fabiani, 23.09.2011 04:49:
> I need to find the max(date) of a field but I need that value later in my
> query.
>
> If I
> select max(x.date_field) as special_date from (select date_field) from table
> where ...)x
>
> I get one row and column.
>
> But now I want to use that field in the rest of the query
>
> select y.*,  max(x.date_field) as special_date from (select date_field) from
> table where ...)x
> from aTable y where y.somefield = special_date.
>
> The above only returns one row and one column the "special_date."
>

Not sure I undersand you correctly, but shouldn't the following be doing what you want:

SELECT y.*
FROM table y
WHERE y.somefield = (SELECT max(x.date_field)
                      FROM table x
                      WHERE ...)

Thomas