max value from join

Поиск
Список
Период
Сортировка
От hook
Тема max value from join
Дата
Msg-id 40CDCBFE.7070608@kcp.com
обсуждение исходный текст
Ответы Re: max value from join
Список 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 usingselect    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.00991164031 | 07/19/2000  | 6          | Bond    |    0.00
 



thanks




В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Datetime problem
Следующее
От: "Eric Lemes"
Дата:
Сообщение: RES: Datetime problem