Hello friends, In this article we are going to learn how to delete set of duplicate rows with in a table. Which is very common interview question.
Suppose we have a table with number of duplicate rows. We want to kept only one unique row with in the table. We will delete these duplicate rows with the help of CTE (Common table expression).
First of all we will create a dummy table. Insert few record(s) with redundant rows. Then we will apply sql Server ROW_NUMBER() method with PARTITION BY to find numbering of duplicate records. and finally we will apply cte over ROW_NUMBER() and delete rows from CTE. Which will delete rows from table in database.
Step 1). Create a table dbo.StudentMaster.
CREATE TABLE dbo.StudentMaster(StudentId int, StudentName varchar(200), ClassId int, SectionId int, SessionId int)
Go
Step 2). Insert rows with duplicate values into table dbo.StudentMaster.
INSERT INTO dbo.StudentMaster VALUES(21,'Chanchal',109,109,201)
INSERT INTO dbo.StudentMaster VALUES(1,'Amit Kumar',25,89,101),(1,'Amit Kumar',25,89,101),(1,'Amit Kumar',25,89,101)
INSERT INTO dbo.StudentMaster VALUES(20,'Rohit Kumar',29,99,111),(20,'Rohit Kumar',29,99,111),(20,'Rohit Kumar',29,99,111),(20,'Rohit Kumar',29,99,111)
INSERT INTO dbo.StudentMaster VALUES(210,'Aarti',209,109,101),(210,'Aarti',209,109,101),(210,'Aarti',209,109,101),(210,'Aarti',209,109,101)
INSERT INTO dbo.StudentMaster VALUES(211,'Roshan',119,229,121)
Go
View Table Rows.
SELECT * FROM dbo.StudentMaster
Go
OUTPUT: StudentId StudentName ClassId SectionId SessionId ----------- ------------- ----------- ----------- ----------- 21 Chanchal 109 109 201 1 Amit Kumar 25 89 101 1 Amit Kumar 25 89 101 1 Amit Kumar 25 89 101 20 Rohit Kumar 29 99 111 20 Rohit Kumar 29 99 111 20 Rohit Kumar 29 99 111 20 Rohit Kumar 29 99 111 210 Aarti 209 109 101 210 Aarti 209 109 101 210 Aarti 209 109 101 210 Aarti 209 109 101 211 Roshan 119 229 121 (13 rows affected)
Step 3). Apply ROW_NUMBER() Method to applying numbering for duplicate rows.
SELECT ROW_NUMBER() OVER(Partition By StudentId ORDER BY StudentId) No, * FROM dbo.StudentMaster
Go
OUTPUT: No StudentId StudentName ClassId SectionId SessionId -------------------- ----------- --------------- ----------- ----------- ----------- 1 1 Amit Kumar 25 89 101 2 1 Amit Kumar 25 89 101 3 1 Amit Kumar 25 89 101 1 20 Rohit Kumar 29 99 111 2 20 Rohit Kumar 29 99 111 3 20 Rohit Kumar 29 99 111 4 20 Rohit Kumar 29 99 111 1 21 Chanchal 109 109 201 1 210 Aarti 209 109 101 2 210 Aarti 209 109 101 3 210 Aarti 209 109 101 4 210 Aarti 209 109 101 1 211 Roshan 119 229 121 (13 rows affected)
Step 4). Applying CTE (Common table expression) over ROW_NUMBER() method.
;with CTE AS(
SELECT ROW_NUMBER() OVER(Partition By StudentId ORDER BY StudentId) No, * FROM dbo.StudentMaster)
SELECT * from CTE WHERE No=1
Go
OUTPUT: No StudentId StudentName ClassId SectionId SessionId -------------------- ----------- --------------- ----------- ----------- ----------- 1 1 Amit Kumar 25 89 101 1 20 Rohit Kumar 29 99 111 1 21 Chanchal 109 109 201 1 210 Aarti 209 109 101 1 211 Roshan 119 229 121 (5 rows affected)
Step 5). Finally we delete rows from cte having no greater than 1
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY StudentId ORDER BY StudentId) [No], * FROM dbo.StudentMaster)
DELETE FROM CTE WHERE [No]>1
Go
View data from dbo.StudentMaster
SELECT * FROM dbo.StudentMaster
Go
OUTPUT: StudentId StudentName ClassId SectionId SessionId ----------- ----------------- ----------- ----------- ----------- 21 Chanchal 109 109 201 1 Amit Kumar 25 89 101 20 Rohit Kumar 29 99 111 210 Aarti 209 109 101 211 Roshan 119 229 121 (5 rows affected)
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.