TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Abraham Olatubosun
NA
471
113.5k
How to remove Duplicate rows retain latest LastDrugPickup
Apr 19 2017 8:43 PM
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
Reply
Answers (
8
)
Group by with order by error
about SQL Full Course