Thread: max value from join

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

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