SQL query - Join that returns the first two records of joining table

Question

I have two tables:

Patient

• pkPatientId
• FirstName
• Surname

PatientStatus

• pkPatientStatusId
• fkPatientId
• StatusCode
• StartDate
• EndDate

Patient -> PatientStatus is a one to many relationship.

I am wondering if its possible in SQL to do a join which returns only the first two PatientStatus records for each Patient. If only one PatientStatus record exists then this should not be returned in the results.

The normal join of my query is:

SELECT FROM Patient p INNER JOIN PatientStatus ps ON p.pkPatientId = ps.fkPatientId
ORDER BY ps.fkPatientId, ps.StartDate

Answer 1

A CTE is probably your best bet if you're in SQL Server 2005 or greater, but if you want something a little more compatible with other platforms, this should work:

SELECT
     P.pkPatientID,
     P.FirstName,
     P.LastName,
     PS1.StatusCode AS FirstStatusCode,
     PS1.StartDate AS FirstStatusStartDate,
     PS1.EndDate AS FirstStatusEndDate,
     PS2.StatusCode AS SecondStatusCode,
     PS2.StartDate AS SecondStatusStartDate,
     PS2.EndDate AS SecondStatusEndDate
FROM
     Patient P
INNER JOIN PatientStatus PS1 ON
     PS1.fkPatientID = P.pkPatientID
INNER JOIN PatientStatus PS2 ON
     PS2.fkPatientID = P.pkPatientID AND
     PS2.StartDate > PS1.StartDate
LEFT OUTER JOIN PatientStatus PS3 ON
     PS3.fkPatientID = P.pkPatientID AND
     PS3.StartDate < PS1.StartDate
LEFT OUTER JOIN PatientStatus PS4 ON
     PS4.fkPatientID = P.pkPatientID AND
     PS4.StartDate > PS1.StartDate AND
     PS4.StartDate < PS2.StartDate
WHERE
     PS3.pkPatientStatusID IS NULL AND
     PS4.pkPatientStatusID IS NULL

It does seem a little odd to me that you would want the first two statuses instead of the last two, but I'll assume that you know what you want.

You can also use WHERE NOT EXISTS instead of the PS3 and PS4 joins if you get better performance with that.