Postgres Pro
Downloads
Home   >   mailing lists

Thread: max question

max question

From
"A. R. Van Hook"
Date:
I have the following in a table:  oid   |   citkey   |  contby  |  contdate  | abcontinue |   ccdate  
---------+------------+----------+------------+------------+------------5774835 | 04-0594703 |          |            |
         |5775325 | 04-0594703 | Attorney | 04/06/2005 | 6          | 03/07/20055776060 | 04-0594703 | Attorney |
05/04/2005| 6          | 04/05/20055776067 | 04-0594703 | Attorney | 05/04/2005 | 6          | 04/05/2005
 

I am trying to pull rows that have the max. contdate. Why does the 
following give more than 2 rows?ql "select oid,* from ccontinue where citkey ='04-0594703' group by 
oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) = 
contdate"  oid   |   citkey   |  contby  |  contdate  | abcontinue |   ccdate  
---------+------------+----------+------------+------------+------------5776067 | 04-0594703 | Attorney | 05/04/2005 |
6         | 04/05/20055775325 | 04-0594703 | Attorney | 04/06/2005 | 6          | 03/07/20055776060 | 04-0594703 |
Attorney| 05/04/2005 | 6          | 04/05/2005
 
(3 rows)

thanks

Re: max question

From
Tom Lane
Date:
"A. R. Van Hook" <hook@lake-lotawana.mo.us> writes:
> I am trying to pull rows that have the max. contdate. Why does the 
> following give more than 2 rows?
>  ql "select oid,* from ccontinue where citkey ='04-0594703' group by 
> oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) = 
> contdate"

HAVING is going to interpret the max() aggregate separately for each
group ... that is, the above query asks for all the rows that have the
largest contdate within their group.  Given that OID is one of the
grouping columns, I'd pretty much expect that to select every single
row in the table, because each row will form its own unique group :-(

What is it you are trying to accomplish here?  In particular, what
led to that choice of GROUP BY?
        regards, tom lane

Re: max question

From
"Dinesh Pandey"
Date:
Try

"select oid,* from ccontinue where citkey ='04-0594703' group by
oid,citkey,contby,contdate,abcontinue,ccdate having contdate= max(contdate)"

Thanks
Dinesh Pandey

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of A. R. Van Hook
Sent: Tuesday, April 12, 2005 10:54 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] max question

I have the following in a table:  oid   |   citkey   |  contby  |  contdate  | abcontinue |   ccdate  
---------+------------+----------+------------+------------+------------5774835 | 04-0594703 |          |            |
         |5775325 | 04-0594703 | Attorney | 04/06/2005 | 6          | 03/07/20055776060 | 04-0594703 | Attorney |
05/04/2005| 6          | 04/05/20055776067 | 04-0594703 | Attorney | 05/04/2005 | 6          | 04/05/2005
 

I am trying to pull rows that have the max. contdate. Why does the 
following give more than 2 rows?ql "select oid,* from ccontinue where citkey ='04-0594703' group by 
oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) = 
contdate"  oid   |   citkey   |  contby  |  contdate  | abcontinue |   ccdate  
---------+------------+----------+------------+------------+------------5776067 | 04-0594703 | Attorney | 05/04/2005 |
6         | 04/05/20055775325 | 04-0594703 | Attorney | 04/06/2005 | 6          | 03/07/20055776060 | 04-0594703 |
Attorney| 05/04/2005 | 6          | 04/05/2005
 
(3 rows)

thanks


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faq

Re: max question

From
Bruno Wolff III
Date:
On Tue, Apr 12, 2005 at 12:24:21 -0500, "A. R. Van Hook" <hook@lake-lotawana.mo.us> wrote:
> I have the following in a table:
>   oid   |   citkey   |  contby  |  contdate  | abcontinue |   ccdate  
> ---------+------------+----------+------------+------------+------------
> 5774835 | 04-0594703 |          |            |            |
> 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6          | 03/07/2005
> 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6          | 04/05/2005
> 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6          | 04/05/2005
> 
> I am trying to pull rows that have the max. contdate. Why does the 
> following give more than 2 rows?
> ql "select oid,* from ccontinue where citkey ='04-0594703' group by 
> oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) = 
> contdate"

If you group by oid you are going to get a separate group for each row.
You haven't told us what you actually expect for output, so I don't
have specific advise for a query. However, you might find the Postgres
extension DISTINCT ON useful, depending on exactly what you want.

>   oid   |   citkey   |  contby  |  contdate  | abcontinue |   ccdate  
> ---------+------------+----------+------------+------------+------------
> 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6          | 04/05/2005
> 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6          | 03/07/2005
> 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6          | 04/05/2005
> (3 rows)
> 
> thanks
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>               http://www.postgresql.org/docs/faq