i.e. something like this fragment:
FROM Patient p
LEFT JOIN (
SELECT [PatientID]
,[CodeValue]
,rowNum = ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY PatientIdentifierID DESC)
FROM [PatientIdentifier]
where CodeName = 'MRN'
) pid on pid.PatientID = p.patientID and pid.rowNum = 1