From: Richard Poole <rp@guests.deus.net>
To: pgsql-sql@postgresql.org
Subject: Re: max value from join
Date: Mon, 14 Jun 2004 19:26:17 +0100
Message-ID: <20040614182617.GA6017@guests.deus.net> (raw)
In-Reply-To: <40CDCBFE.7070608@kcp.com>
References: <40CDCBFE.7070608@kcp.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rp@guests.deus.net
Subject: Re: max value from join
In-Reply-To: <20040614182617.GA6017@guests.deus.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox