Is there a way to only pull the record with the most recent date if there are several similar records in the table?
I have two tables, Candidates and Candidate_Activity. I'm looking for the date of an activity in the CA table. There may be many activities of the type I'm searching for, but I only need to know the date of the most recent one.
My basic search is:
SELECT CA.Entry_Date AS Date, C.Candidate_Id, C.First_Name, C.Last_Name,
C.Phone_1 AS Phone, C.Email_Address AS Email
FROM dbo.Candidates C INNER JOIN
dbo.Candidate_Activity CA ON C.Candidate_Id = CA.Candidate_Id
WHERE (CA.Act_Type = 'LT App Sig')
ORDER BY CA.Entry_Date DESC, C.Last_Name, C.First_Name
But that could/would pull all the LT App Sig activities for each Candidate. There may be one dated 4/23/2001, another dated 3/31/2005, another dated 6/20/2012. I only want the most recent date.
Does that make sense? Can it be done?
PS...my db is an .adp