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 D3F485343A for ; Wed, 13 Apr 2005 02:05:31 -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 87202-04 for ; Wed, 13 Apr 2005 05:05:29 +0000 (GMT) Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244]) by svr1.postgresql.org (Postfix) with ESMTP id 1C639533A8 for ; Wed, 13 Apr 2005 02:05:28 -0300 (ADT) Received: from dinesh by secf.com (MDaemon.PRO.v7.2.2.R) with ESMTP id md50000619947.msg for ; Tue, 12 Apr 2005 22:05:18 -0700 Reply-To: From: "Dinesh Pandey" To: "'A. R. Van Hook'" , Subject: Re: max question Date: Wed, 13 Apr 2005 10:32:39 +0530 Organization: Second Foundation India P Ltd. MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-reply-to: <425C0445.8020603@lake-lotawana.mo.us> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcU/5gWq15QKkGqHTrSbMGaEak5npgAABenA X-Authenticated-Sender: dpandey@secf.com X-Spam-Processed: mail.secf.com, Tue, 12 Apr 2005 22:05:18 -0700 (not processed: message from valid local sender) X-MDRemoteIP: 220.227.32.11 X-Return-Path: dpandey@secf.com X-MDaemon-Deliver-To: pgsql-sql@postgresql.org X-MDAV-Processed: mail.secf.com, Tue, 12 Apr 2005 22:05:21 -0700 Message-Id: <20050413050528.1C639533A8@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.414 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200504/182 X-Sequence-Number: 21075 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/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" 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