CODEWITHSUNDEEP
sqlsql-servert-sql
bluefox
bluefox

Reputation: 156

SQL - Remove Duplicate Rows From Table

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:

Duplicate Data

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?

Upvotes: 3

Views: 135

Answers (2)

tomc
tomc

Reputation: 1207

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"

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

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

Upvotes: 3

Related Questions