From: | John Fabiani <johnf(at)jfcomputer(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: using the aggregate function max() |
Date: | 2011-09-23 04:40:16 |
Message-ID: | 201109222140.16949.johnf@jfcomputer.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thursday, September 22, 2011 08:14:58 pm David Johnston wrote:
> On Sep 22, 2011, at 22:49, John Fabiani <johnf(at)jfcomputer(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Amar Dhole | 2011-09-23 06:32:13 | use of savepoint in containter managed transaction |
Previous Message | David Johnston | 2011-09-23 03:14:58 | Re: using the aggregate function max() |