X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 96B1D53379 for ; Wed, 13 Apr 2005 01:47:19 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 80756-04 for ; Wed, 13 Apr 2005 04:47:17 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id A041E5343A for ; Wed, 13 Apr 2005 01:47:15 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3D4kbn3003274; Wed, 13 Apr 2005 00:46:37 -0400 (EDT) To: "A. R. Van Hook" Cc: pgsql-sql@postgresql.org Subject: Re: max question In-reply-to: <425C0445.8020603@lake-lotawana.mo.us> References: <425C0445.8020603@lake-lotawana.mo.us> Comments: In-reply-to "A. R. Van Hook" message dated "Tue, 12 Apr 2005 12:24:21 -0500" Date: Wed, 13 Apr 2005 00:46:37 -0400 Message-ID: <3273.1113367597@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/179 X-Sequence-Number: 21072 "A. R. Van Hook" 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