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 0B2A3D1B19A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 14 Jun 2004 15:28:12 -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 58872-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 14 Jun 2004 18:27:17 +0000 (GMT)
Received: from jalapeno.crazydogs.org (jalapeno.jellybean.co.uk
	[212.78.70.100])
	by svr1.postgresql.org (Postfix) with ESMTP id 74550D1B220
	for <pgsql-sql@postgresql.org>; Mon, 14 Jun 2004 15:26:29 -0300 (ADT)
Received: from rp by jalapeno.crazydogs.org with local (Exim 3.36 #1 (Debian))
	id 1BZw9x-0007sS-00
	for <pgsql-sql@postgresql.org>; Mon, 14 Jun 2004 19:26:17 +0100
Date: Mon, 14 Jun 2004 19:26:17 +0100
From: Richard Poole <rp@guests.deus.net>
To: pgsql-sql@postgresql.org
Subject: Re: max value from join
Message-ID: <20040614182617.GA6017@guests.deus.net>
References: <40CDCBFE.7070608@kcp.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <40CDCBFE.7070608@kcp.com>
User-Agent: Mutt/1.5.4i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No,
 hits=1.1 tagged_above=0.0 required=5.0 tests=NO_DNS_FOR_FROM
X-Spam-Level: *
X-Archive-Number: 200406/116
X-Sequence-Number: 18061

On Mon, Jun 14, 2004 at 11:02:06AM -0500, hook wrote:
> I have a court program with related tables

<snip>

> 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??

A subselect may be useful to you:

SELECT c.citkey, t.contDate  -- other fields...
FROM citation c, ccontinue t -- other tables...
WHERE c.citkey = t.citkey    -- other join clauses...
AND t.contDate = (
	SELECT max(contDate) FROM ccontinue
)
-- no need for GROUP BY / HAVING
ORDER BY c.citkey


Richard