In my Query below, I get back two records in my dataset. My issue is I am only filtering on one relationship type in the table, however there are several other relationship types I am not concerned with pulling back. So in my test patient below, Its pulling one with a ListOrder = to 1 and one with ListOrder =5.
What I need is to pull the Top 1 (Max) based of the created date and and make that the first one. I then need the next most recent one created and make that #2 and then finally the third most recent one based off Created.
I hope this makes sense. If not, I can explain more.
INNER JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
INNER JOIN Medlists ml ON pr.RelationshipTypeMId = ml.MedlistsId
pr.PatientProfileId = '672' AND
pr.Type = 5 -- filter for personal contact type