I am attempting to pull a single record with the most recent affective date. However, I am getting two records with effective dates of 04/01/2010 and 12/01/2009. Can someone please tell me how to pull only the record with an effective date of 04/01/2010? Thanks for your help.
select
T1.idNumber, T1.lastname, T1.firstname, T1.streetaddress,
T1.city, T1.state, T1.zipcode, T1.DOB, T1.DOD,
T2.mNumber, countycode, max(T2.effectiveDate)
from
PTable T1,
MTable T2
where
T1.idNumber in ('12345')
group by
T1.idNumber, T1.lastname, T1.firstname, T1.streetaddress,
T1.city, T1.state, T1.zipcode, T1.DOB, T1.DOD,
T2.mNumber, T1.countycode