From: John Fabiani <johnf@jfcomputer.com>
Organization: J.F. Computer Solutions
To: pgsql-sql@postgresql.org
Subject: Re: using the aggregate function max()
Date: Thu, 22 Sep 2011 21:40:16 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.34.10-0.2-desktop; KDE/4.4.4; i686; ; )
References: <201109221949.20671.johnf@jfcomputer.com>
	<5EABD4DE-5CB8-4C65-A1FF-3166A683EE3F@yahoo.com>
In-Reply-To: <5EABD4DE-5CB8-4C65-A1FF-3166A683EE3F@yahoo.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201109222140.16949.johnf@jfcomputer.com>

On Thursday, September 22, 2011 08:14:58 pm David Johnston wrote:
> On Sep 22, 2011, at 22:49, John Fabiani <johnf@jfcomputer.com> wrote:
> > 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
> 
> Your query above is syntactically invalid.  Try this.
> 
> WITH max_date AS ( select max(datefield) AS specialdate from ...)
> SELECT *
> FROM table
> JOIN max_date ON table.somefield = max_date.specialdate;
> 
> You can use a online query instead of the WITH if desired, same effect.
> 
> You could also drop the join and use the max_date CTE in a WHERE clause:
> 
> ... FROM table WHERE table.somefield = (SELECT specialdate FROM max_date)
> 
> David J

thanks - I'll look into the "with"
Johnf