From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | John Fabiani <johnf(at)jfcomputer(dot)com> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: using the aggregate function max() |
Date: | 2011-09-23 03:14:58 |
Message-ID: | 5EABD4DE-5CB8-4C65-A1FF-3166A683EE3F@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)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 = 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
From | Date | Subject | |
---|---|---|---|
Next Message | John Fabiani | 2011-09-23 04:40:16 | Re: using the aggregate function max() |
Previous Message | John Fabiani | 2011-09-23 02:49:20 | using the aggregate function max() |