Thread

1. max value from join

   hook <hook@kcp.com> — 2004-06-14T16:02:06Z

   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
   
   
   
   

2. Re: max value from join

   Richard Poole <rp@guests.deus.net> — 2004-06-14T18:26:17Z

   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