On Thursday, September 22, 2011 08:14:58 pm David Johnston wrote: > On Sep 22, 2011, at 22:49, John Fabiani <jo...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
