Re: max value from join

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