From: John Fabiani <johnf@jfcomputer.com>
Organization: J.F. Computer Solutions
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: using the aggregate function max()
Date: Thu, 22 Sep 2011 19:49:20 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.34.10-0.2-desktop; KDE/4.4.4; i686; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201109221949.20671.johnf@jfcomputer.com>

Hi,
I need a little help understanding how to attack this problem.  

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."

How can I use the aggregate field "special_date" in the rest of the query?  Or 
is there some other way?

Johnf