Get Customer and most recent status

I'm just posting this solution for my own historical records.

My problem: Getting each candidate and thier most recent status...

Consider the following tables:

Candidates
===========
CandidateID
FirstName
LastName

CollegeCandidateXREFLog
===================
XREFID (key, unique, autogenerated)
CollegeID
CandidateID
StatusID
Create_Date


SELECT a.FirstName, a.LastName, b.StatusIDFROM Candidates aLEFT OUTER JOIN CollegeCandidateXREFLog bON a.CandidateID = b.CandidateIDWHERE
(
XREFID IS NULL -- accounting for those with no status record
OR XREFID =
(
SELECT MAX(XREFID)
FROM CollegeCandidateXREFLog
WHERE CandidateID = a.CandidateID
)
)

Thanks for the solution Bryan!