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