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 57833D1B19C for ; Mon, 14 Jun 2004 13:02:25 -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 40288-05 for ; Mon, 14 Jun 2004 16:02:16 +0000 (GMT) Received: from mailhost1.os.kcp.com (mailhost1.os.kcp.com [198.124.201.31]) by svr1.postgresql.org (Postfix) with ESMTP id 6A727D1B16F for ; Mon, 14 Jun 2004 13:02:13 -0300 (ADT) Message-ID: <40CDCBFE.7070608@kcp.com> Date: Mon, 14 Jun 2004 11:02:06 -0500 From: hook User-Agent: Mozilla Thunderbird 0.5 (X11/20040208) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: max value from join Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-MessageInspectorSig: 6fbd6a29e9c926b3210b37ac6b45aad3 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/107 X-Sequence-Number: 18052 I have a court program with related tables citation citkey varchar(16) NOT NULL PRIMARY KEY, .... cdefendant citkey varchar(16) NOT NULL PRIMARY KEY references citation, .... ccourt citkey varchar(16) NOT NULL PRIMARY KEY references citation, .... disposition citkey varchar(16) NOT NULL PRIMARY KEY references citation, .... ccontinue citkey varchar(16) NOT NULL references citation, .... warrant citkey varchar(16) NOT NULL references citation, .... I am trying to extract data related to the last conttinue date using select c.citkey, /* c.cdate, c.badge, c.vioDesc, b.lname, b.fname, b.mi, b.race, b.dob, b.sex, d.docket, d.plea, d.fine, d.costs, d.ddate, d.abdocket, d.bond, p.disDate, p.disDesc, p.disCode, p.amount, */ t.contDate, t.abcontinue, w.bndType, w.bndAmt from citation c, cdefendant b, ccourt d, ccontinue t, disposition p, warrant w where c.citkey = b.citkey and b.citkey = d.citkey and d.citkey = t.citkey and t.citkey = p.citkey and p.citkey = w.citkey group by c.citkey, c.cdate, c.badge, c.vioDesc, b.lname, b.fname, b.mi, b.race, b.dob, b.sex, d.docket, d.plea, d.fine, d.costs, d.ddate, d.abdocket, d.bond, p.disDate, p.disDesc, p.disCode, p.amount, t.abcontinue, t.contDate, w.bndType, w.bndAmt having max(t.contDate) = t.contDate order by c.citkey I cannot seem to get unique rows with only the max contDate?? i.e. citkey | contdate | abcontinue | bndtype | bndamt ------------+-------------+------------+---------+--------- 991164031 | 06/07/2000 | 6 | Bond | 0.00 991164031 | 07/19/2000 | 6 | Bond | 0.00 thanks