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?

You want delete? or remove it from a select? what is your rdbms? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jan 23, 2016 at 4:26
postgres, oracle or sql server? How is you have two field name PatientID ? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jan 23, 2016 at 4:27
Sql server... The PatientID fields are the same columns. I'm comparing them against themselves. — bluefox
– bluefox, Commented Jan 23, 2016 at 4:30
I'm trying to show two pairs of patients who have the same doctor. — bluefox
– bluefox, Commented Jan 23, 2016 at 4:31

Juan Carlos Oropeza · Accepted Answer · 2016-01-23 04:33:28Z

3

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

answered Jan 23, 2016 at 4:33

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tomc · Accepted Answer · 2016-01-23 04:35:52Z

1

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"

answered Jan 23, 2016 at 4:35

tomc

1,2377 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL - Remove Duplicate Rows From Table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related