Postgres Pro
Downloads
Home   >   mailing lists

Thread: using the aggregate function max()

using the aggregate function max()

From
John Fabiani
Date:
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

Re: using the aggregate function max()

From
David Johnston
Date:

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

Re: using the aggregate function max()

From
John Fabiani
Date:
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

use of savepoint in containter managed transaction

From
Amar Dhole
Date:
HI All,

I have requirement to use save point. But I am using session bean whose transaction is managed by container. While
executingsql insert I got unique key constraint because of this my other command following this exception got ignored.
Iwant to continue my transaction even after this as we ignore this insert and make an entry in other table for this
error.And proceed head. with Postgres this is not possible. I cant user plsql block as behaviour is spread in all my
application. Need some suggestion to achieve this with savepoint.  

I have some question.
1) Can I use savepoint in Container Managed transaction ?
2) Can I make some custom change to jdbc driver to ignore duplicate key exception or to raise warning instead ERROR.

Thanks
Amar

Re: using the aggregate function max()

From
Thomas Kellerer
Date:
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