This article walks you through how to delete multiple duplicate rows..

CREATE DATAVASE MANOJ

--------

USE MANOJ

------

CREATE TABLE AKKI

(ID INT,

FNAME VARCHAR(50),

LNAME VARCHAR(50)

)

------

insert into AKKI select 1,'Jen','Ambelang'

insert into AKKI select 11,'Jiong','Hong'

insert into AKKI select 25,'Sandra','Mator'

insert into AKKI select 35,'Chun','Chang'

insert into AKKI select 21,'Yuki','Fukushima'

insert into AKKI select 1,'Jen','Ambelang'

insert into AKKI select 1,'Jen','Ambelang'

insert into AKKI select 25,'Sandra','Mator'

insert into AKKI select 25,'Sandra','Mator'

--------

SELECT * FROM AKKI ORDER BY ID

---------

The first step in deleting duplicate rows is to generate a unique row id for the entire table. This can be done using the Row_Number() function.

 

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

Step 2

The second step in deleting duplicate rows is to generate unique row ids for every group.

Now, by using a co-related sub-query we can produce unique row ids for each group.

SELECT ROW,GROUPROW= CASE WHEN ID=ID

THEN

(

SELECT COUNT(*) FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY ID

) AS ROW,ID,LNAME,FNAME FROM AKKI

) AS A

WHERE A.ID=B.ID AND A.ROW<B.ROW)+1

END ,ID,FNAME,LNAME FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

)AS B

---------
--------By using a co-related sub-query we genrate unique row id for each row--- row grouprow id fname lname
1 1 1 Jen Ambelang
2 2 1 Jen Ambelang
3 3 1 Jen Ambelang
4 1 11 Jiong Hong
5 1 21 Yuki Fukushima
6 1 25 Sandra Mator
7 2 25 Sandra Mator
8 3 25 Sandra Mator
9 1 35 Chun Chang

 

Step 3

The last step in deleting duplicate rows is to use the common table expression, as shown below

WITH DUPLICATE(ROW,GROUPROW,ID,FNAME,LNAME)

AS

(

SELECT ROW,GROUPROW= CASE WHEN ID=ID

THEN

(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

) AS A WHERE A.ID=B.ID AND

A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER

BY ID) AS ROW,

ID,LNAME,FNAME FROM AKKI

)AS B

)

DELETE FROM DUPLICATE WHERE GROUPROW<>1
---(DELETE FROM DUPLICATE WHERE GROUPROW<>1--(to remove the duplicate records))

--------

VERIFY THE OUTPUT

--------

SELECT * FROM AKKI
----------
select * from AKKI-----GROUPROW IS ALIAS WHICH USE IN THE SELECT QUERY
ID FNAME LNAME
1 Jen Ambelang
11 Jiong Hong
25 Sandra Mator
35 Chun Chang
21 Yuki Fukushima

Step 1