SQL - Remove Duplicate Rows From Table

Question

How can I remove duplicate rows from my table? I've searched the Internet, but I haven't been able to solve the issue. Here's what I've written:

WITH C As
(
Select A.PatientID, A.DoctorID
From Appointment As A
)

Select Distinct A.PatientID, A2.PatientID, A.DoctorID
From Appointment As A
    Inner Join C as A2
        On A.DoctorID = A2.DoctorID
Where A.PatientID <> A2.PatientID
Order By A.PatientID Asc

Here's the outcome:

In the image above, you'll notice that the data in row 1 is duplicated in row 6. How can I remove all the duplicate rows? Any suggestions?

Answer 1

You dont need a CTE for this

Try

 SELECT DISTINCT PatientId, PatientId, DoctorID
 FROM Appointment A1
 JOIN Appointment A2
   ON A1.PatientId < A2.PatientId
  AND A1.DoctorID = A2.DoctorID
 Order By A1.PatientID Asc

Answer 2

You can not generate the symmetric dups in the first place by arbitrarily choosing patient A to allways be the one of the pair with the smaller ID

...
Where A.PatientID < A2.PatientID

This will not help if you have dups in the original table but by its name it should be a primary key and/or have a not NULL & unique index on "PatientID"