Re: max value from join

From: Richard Poole <rp(at)guests(dot)deus(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: max value from join
Date: 2004-06-14 18:26:17
Message-ID: 20040614182617.GA6017@guests.deus.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2004-06-14 18:26:55 Re: query with =ALL
Previous Message Jaime Casanova 2004-06-14 18:06:37 Re: query with =ALL