Tech
Forums
Jobs
Books
Events
Videos
Conference
Annual Conference
Bcrypt
Ai Conference
Live
More
Interviews
Certification
Training
Career
Members
News
Blogs
Post
An Article
A Blog
A Video
An Ebook
An Interview Question
Register
Login
8
Reply
How to remove Duplicate rows retain latest LastDrugPickup
Abraham Olatubosun
7 years ago
377
Reply
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.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
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
Post
Reset
Cancel
Answers (
8
)
Next Recommended Forum
Group by with order by error
about SQL Full Course