DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:Mime-Version:In-Reply-To:X-Apple-Yahoo-Original-Message-Folder:Content-Type:Content-Transfer-Encoding:Message-Id:Cc:X-Mailer:From:X-Apple-Yahoo-Replied-Msgid:Subject:Date:To;
	b=JBm3dRRCBMVCDhnsjjgJXtUcB7pyAKQUAZoL3BX1NGevYmyoQme6dsGfxpzXsn8KExStNCgDsJEtcNLojBvqpDPBYIVJj/sQPXFUeD4c/LD9nE74gZHKNSrWye2IBOEq/Fck3yRnDo4AV+Hp7EoBNnxxdy4actYRS1H/isCN0f0=
	;
References: <201109221949.20671.johnf@jfcomputer.com>
Mime-Version: 1.0 (iPad Mail 8J2)
In-Reply-To: <201109221949.20671.johnf@jfcomputer.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-Id: <5EABD4DE-5CB8-4C65-A1FF-3166A683EE3F@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: using the aggregate function max()
Date: Thu, 22 Sep 2011 23:14:58 -0400
To: John Fabiani <johnf@jfcomputer.com>


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. =20
>=20
> I need to find the max(date) of a field but I need that value later in my=20=

> query.
>=20
> If I
> select max(x.date_field) as special_date from (select date_field) from tab=
le=20
> where ...)x=20
>=20
> I get one row and column.
>=20
> But now I want to use that field in the rest of the query
>=20
> select y.*,  max(x.date_field) as special_date from (select date_field) fr=
om=20
> table where ...)x =20
> from aTable y where y.somefield =3D special_date.
>=20
> The above only returns one row and one column the "special_date."
>=20
> How can I use the aggregate field "special_date" in the rest of the query?=
  Or=20
> is there some other way?
>=20
> Johnf
>=20
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

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 =3D 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 =3D (SELECT specialdate FROM max_date)

David J