How to delete duplicated rows and retain a single row with certain value in SQL SERVER

Question

I've got a table like this

DoctorName | IsOperDoc
  Albert   |     1
  Albert   |     1
  Albert   |     0
   Bill    |     0
   Bill    |     0
  Charlie  |     0
   Dave    |     1

I would like to delete duplicated rows and retain rows with IsOperDoc = 1 (also retains single row with IsOperDoc = 0 if the Doc is not OperDoc)

What I expect

DoctorName | IsOperDoc
  Albert   |     1
   Bill    |     0
  Charlie  |     0
   Dave    |     1

Do you have an `ID` column in that table? – juergen d Nov 05 '19 at 09:06 — juergen d, Nov 05 '19 at 09:06

score 2 · Answer 1 · answered Nov 05 '19 at 09:13

2

Try this:

WITH cte AS (
    SELECT  
        DoctorName , 
        IsOperDoc, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                DoctorName , 
                IsOperDoc
            ORDER BY 
                DoctorName , 
                IsOperDoc
        ) row_num
     FROM 
        yourTable
)
DELETE FROM cte
WHERE row_num > 1;

answered Nov 05 '19 at 09:13

Ankit Das

640
4
16

It works ,thank you for teaching me about ROW_NUMBER() – Chen Nov 06 '19 at 01:49

score 1 · Accepted Answer · answered Nov 05 '19 at 09:15

Try this:

DECLARE @DataSource TABLE
(
    [DoctorName] NVARCHAR(128)
   ,[IsOperDoc] TINYINT
);

INSERT INTO @DataSource ([DoctorName], [IsOperDoc])
VALUES ('Albert', 1)
      ,('Albert', 1)
      ,('Albert', 0)
      ,('Bill', 0)
      ,('Bill', 0)
      ,('Charlie', 0)
      ,('Dave', 1);

WITH DataSource AS
(
    SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY [DoctorName] ORDER BY [IsOperDoc] DESC) AS [Rank]
    FROM @DataSource
)
DELETE FROM DataSource
WHERE [Rank] > 1;

SELECT *
FROM @DataSource;

It works ,thank you for teaching me about ROW_NUMBER() – Chen Nov 06 '19 at 01:49 — Chen, Nov 06 '19 at 01:49

How to delete duplicated rows and retain a single row with certain value in SQL SERVER

2 Answers2