left outer join (select referral_id,max(NOTIF_DATETIME) as lastActivity
,referral_id
from  JOIN KPGA_ODS.CLARITY.REFERRAL_NOTIF_HIS
group by referral_id) as nh

quote:

---

I have the following query, intended to get the MAX (NOTIF_DATETIME) AS Last_Activity -

-----------------------------------------------------------
Select distinct R.REFERRAL_ID,H.NOTE_SUMMARY,nh.NOTIF_ACT_C,
Z.NAME,MAX(nh.NOTIF_DATETIME) as Last_Activity

From KPGA_ODS.CLARITY.REFERRAL R
left outer Join KPGA_ODS.CLARITY.PATIENT P On R.PAT_ID = P.PAT_ID
left outer Join KPGA_ODS.CLARITY.NOTES_MC_RFL N On R.REFERRAL_ID = N.RECORD_ID
left outer Join KPGA_ODS.CLARITY.HNO_ENC_INFO H On N.NOTE_ID = H.NOTE_ID
left outer Join KPGA_ODS.CLARITY.ZC_NOTE_TYPE Z On H.NOTE_TYPE_C = Z.NOTE_TYPE_C
left outer JOIN KPGA_ODS.CLARITY.REFERRAL_NOTIF_HIS as nh on nh.REFERRAL_ID = R.REFERRAL_ID
--inner join KPGA_ODS.CLARITY.CLARITY_EMP E ON
Where EXTERNAL_ID_NUM = '201181380'
AND H.NOTE_SUMMARY <> 'NULL'
AND H.NOTE_SUMMARY NOT IN ('Auto: Notification Recipient List')
GROUP BY R.REFERRAL_ID,H.NOTE_SUMMARY,nh.NOTIF_ACT_C,
Z.NAME
--------------------------------------------------------------

Im interested in returning the MAX(NOTIF_DATETIME)as Last_Activity.
However, this is what i get for the Last_Activity column even with the MAX function around it -


Last_Activity
8/6/2010 10:38
8/6/2010 23:00
8/6/2010 10:41
8/6/2010 10:41
8/6/2010 10:41
8/6/2010 10:41
8/6/2010 10:41


I expected to only get th MAX Last_Activity which should be 8/6/2010 23:00.

what I'm I doing wrong??

---