Select every second record then determine earliest date

I have table that looks like the following

I have to select every second record per PatientID that would give the following result (my last query returns this result)

I then have to select the record with the oldest date which would be the following (this is the end result I want)

What I have done so far: I have a CTE that gets all the data I need

WITH cte
AS 
(
    SELECT visit.PatientTreatmentVisitID, mat.PatientMatchID,pat.PatientID,visit.RegimenDate AS VisitDate, 
                        ROW_NUMBER() OVER(PARTITION BY mat.PatientMatchID, pat.PatientID ORDER BY visit.VisitDate ASC) AS RowNumber
    FROM tblPatient pat INNER JOIN tblPatientMatch mat ON mat.PatientID = pat.PatientID
    LEFT JOIN tblPatientTreatmentVisit visit ON visit.PatientID = pat.PatientID
)

I then write a query against the CTE but so far I can only return the second row for each patientID

SELECT *
FROM 
(
    SELECT PatientTreatmentVisitID,PatientMatchID,PatientID, VisitDate,  RowNumber FROM cte    
) as X
WHERE RowNumber = 2

How do I return the record with the oldest date only? Is there perhaps a MIN() function that I could be including somewhere?

Answer

For simplicity add order desc on date column and use TOP to get the first row only

SELECT TOP 1 *
FROM 
(
    SELECT PatientTreatmentVisitID,PatientMatchID,PatientID, VisitDate,  RowNumber FROM cte    
) as X
WHERE RowNumber = 2 
order by VisitDate desc

Advertisement

Answer