SQL Problem

[mellicks2005]

Hello,

I am trying to use a query in CR9 to pull the latest date of a DREXAM for each patient. The patient can have one item or several items. When I run this query, it only pulls back one record, the latest DREXAM from the whole table, not by patients. I need it by patients. Any ideas on what I'm doing wrong?

Here is the query:

select t.tracking_id, t.tracking_group_cd, t.track_event_id, t.requested_dt_tm, t.complete_dt_tm, te.display_key
from V500.TRACKING_EVENT t, v500.track_event te
where t.track_event_id = te.TRACK_EVENT_ID
and te.display_key = 'DREXAM'
and t.complete_dt_tm = (select max(complete_dt_tm) "MaxDate" from V500.TRACKING_EVENT t, v500.track_event te
where t.track_event_id = te.TRACK_EVENT_ID
and te.display_key = 'DREXAM');


Note: The tracking_id field shows by patient. When I run everything up to the last "and" (the complete date), it pulls all rows, which may be more than one per patient. Basically, I just want the latest DREXAM per patient.

Thanks!

[JaganEllis]

There's no reference to the outer query in the sub query. Is track_event_id a unique key on the tracking_event table?