How to remove Duplicate rows retain latest LastDrugPickup

Question

Dear Code masters I believe this mail meet you all in good health i am writting an SQL script that will generate all patient and their most current LastDrugPickupDate. my script is as below SELECT Distinct a.Ptn_Pk a.PatientEnrollmentID AS PatientID CONVERT (datetime a.ARTStartDate 103) AS ARTStartDate CONVERT (datetime c.DispensedByDate 103) AS LastDrugPickup b.Durationb.RegimenType c.RegimenLine FROM dbo.VW_PatientDetail AS a left JOIN dbo.VW_PatientPharmacy AS b ON a.Ptn_Pk = b.Ptn_pk left JOIN dbo.ord_PatientPharmacyOrder AS c ON b.ptn_pk = c.ptn_pk WHERE a.ARTStartDate IS NOT NULL And b.RegimenType is not NULL ORDER BY c.DispensedByDate desc the result is show below with alot of duplicate rows: Please how can i remove the duplicate rows but retaining the most current LastDrugPickDate looking at the yellow shaded records. thank you

Midhun T P · Answer

Hi You may need to use rank() function. Please try below query - SELECT * from( SELECT Distinct a.Ptn_Pk a.PatientEnrollmentID AS PatientID CONVERT(datetime a.ARTStartDate 103) AS ARTStartDate CONVERT(datetime c.DispensedByDate 103) AS LastDrugPickup b.Durationb.RegimenType c.RegimenLine rank() over (partition by a.Ptn_Pk ORDER BY CONVERT(datetime c.DispensedByDate 103) DESC) AS Ranking FROM dbo.VW_PatientDetail AS a left JOIN dbo.VW_PatientPharmacy AS b ON a.Ptn_Pk = b.Ptn_pk left JOIN dbo.ord_PatientPharmacyOrder AS c ON b.ptn_pk = c.ptn_pk WHERE a.ARTStartDate IS NOT NULL And b.RegimenType is not NULL ORDER BY c.DispensedByDate desc ) e WHERE e.ranking=1

Rafnas T P · Answer

I think the result is correct because records are not duplicatedas you can see each reacords are for each RegimenType

Ramesh Barik · Answer

; WITH QResult AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY a.Ptn_Pk a.PatientEnrollmentID ORDER BY c.DispensedByDate ASC ) AS Row a.Ptn_Pk a.PatientEnrollmentID AS PatientID CONVERT(DATETIME a.ARTStartDate 103) AS ARTStartDate CONVERT(DATETIME c.DispensedByDate 103) AS LastDrugPickup b.Duration b.RegimenType c.RegimenLine FROM dbo.VW_PatientDetail AS a LEFT JOIN dbo.VW_PatientPharmacy AS b ON a.Ptn_Pk = b.Ptn_pk LEFT JOIN dbo.ord_PatientPharmacyOrder AS c ON b.ptn_pk = c.ptn_pk WHERE a.ARTStartDate IS NOT NULL AND b.RegimenType IS NOT NULL ) DELETE P FROM dbo.VW_PatientDetail P INNER JOIN QResult ON P.Ptn_Pk = QResult.Ptn_Pk AND P.PatientEnrollmentID = QResult.PatientID where QResult.Row > 1; Try this.

Rafnas T P · Answer

try giving grouup by b.RegimenType before order by

Abraham Olatubosun · Answer

Many thanks Rafnas T P i remove the RegimenType as you instructed and it works but the database is for patient's on Drug and the RegimenType tells the Clinician what drug the patient is currently taking so it is very important for the RegimenType to be on the list out. please kindly advice me. thank you

Rafnas T P · Answer

if not mandatory RegimenType in select query then please remove it you will be getting without duplicate values here the RegimenType are different thats why you are getting duplicate values

Abraham Olatubosun · Answer

Many thanks Joseph Thomas but that didn't work

Joseph Thomas · Answer

Try group by a.Ptn_Pk at the end..