max value from join

hook <hook@kcp.com>

From: hook <hook@kcp.com>
To: pgsql-sql@postgresql.org
Date: 2004-06-14T16:02:06Z
Lists: pgsql-sql
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