max value from join - Mailing list pgsql-sql

From hook
Subject max value from join
Date
Msg-id 40CDCBFE.7070608@kcp.com
Whole thread Raw
Responses Re: max value from join  (Richard Poole <rp@guests.deus.net>)
List 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 usingselect    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.00991164031 | 07/19/2000  | 6          | Bond    |    0.00
 



thanks




pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Datetime problem
Next
From: "Eric Lemes"
Date:
Subject: RES: Datetime problem