pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing the aggregate function max()
4+ messages / 3 participants
[nested] [flat]
* using the aggregate function max()
@ 2011-09-23 02:49 John Fabiani <johnf@jfcomputer.com>
2011-09-23 03:14 ` Re: using the aggregate function max() David Johnston <polobo@yahoo.com>
2011-09-23 06:37 ` Re: using the aggregate function max() Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 2 replies; 4+ messages in thread
From: John Fabiani @ 2011-09-23 02:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: using the aggregate function max()
2011-09-23 02:49 using the aggregate function max() John Fabiani <johnf@jfcomputer.com>
@ 2011-09-23 03:14 ` David Johnston <polobo@yahoo.com>
2011-09-23 04:40 ` Re: using the aggregate function max() John Fabiani <johnf@jfcomputer.com>
1 sibling, 1 reply; 4+ messages in thread
From: David Johnston @ 2011-09-23 03:14 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
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
>
> --
> 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 = 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: using the aggregate function max()
2011-09-23 02:49 using the aggregate function max() John Fabiani <johnf@jfcomputer.com>
2011-09-23 03:14 ` Re: using the aggregate function max() David Johnston <polobo@yahoo.com>
@ 2011-09-23 04:40 ` John Fabiani <johnf@jfcomputer.com>
0 siblings, 0 replies; 4+ messages in thread
From: John Fabiani @ 2011-09-23 04:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: using the aggregate function max()
2011-09-23 02:49 using the aggregate function max() John Fabiani <johnf@jfcomputer.com>
@ 2011-09-23 06:37 ` Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 0 replies; 4+ messages in thread
From: Thomas Kellerer @ 2011-09-23 06:37 UTC (permalink / raw)
To: pgsql-sql
John Fabiani, 23.09.2011 04:49:
> 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."
>
Not sure I undersand you correctly, but shouldn't the following be doing what you want:
SELECT y.*
FROM table y
WHERE y.somefield = (SELECT max(x.date_field)
FROM table x
WHERE ...)
Thomas
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-09-23 06:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-23 02:49 using the aggregate function max() John Fabiani <johnf@jfcomputer.com>
2011-09-23 03:14 ` David Johnston <polobo@yahoo.com>
2011-09-23 04:40 ` John Fabiani <johnf@jfcomputer.com>
2011-09-23 06:37 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox