pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmax value from join
2+ messages / 2 participants
[nested] [flat]
* max value from join
@ 2004-06-14 16:02 hook <hook@kcp.com>
2004-06-14 18:26 ` Re: max value from join Richard Poole <rp@guests.deus.net>
0 siblings, 1 reply; 2+ messages in thread
From: hook @ 2004-06-14 16:02 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: max value from join
2004-06-14 16:02 max value from join hook <hook@kcp.com>
@ 2004-06-14 18:26 ` Richard Poole <rp@guests.deus.net>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Poole @ 2004-06-14 18:26 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-06-14 18:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-14 16:02 max value from join hook <hook@kcp.com>
2004-06-14 18:26 ` Richard Poole <rp@guests.deus.net>
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