Delete duplicate row in Sql Server
Here i use only name column
select * from student1 order by name,age
id name age
----------- --------------------- -----------
7 Ramu Kumar 30
14 Sachi Supriya 25
18 Sachi 28
19 Ramu 29
20 ramu 30
22 ramu 29
insert into student1(name,age) values('ramu',29)
id name age
----------- ------------------------ -----------
19 Ramu 29
22 ramu 29
23 ramu 29
20 ramu 30
7 Ramu Kumar 30
18 Sachi 28
14 Sachi Supriya 25
WITH tblDuplicate as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name ORDER BY Name)
As RowNumber,* FROM student1
)
SELECT * FROM tblDuplicate
RowNumber id name age
------- ------- ----------- ------ --------
1 19 Ramu 29
2 20 ramu 30
3 22 ramu 29
4 23 ramu 29
1 7 Ramu Kumar 30
1 18 Sachi 28
1 14 Sachi Supriya 25
WITH tblDuplicate as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name ORDER BY Name)
As RowNumber,* FROM student1
)
DELETE FROM tblDuplicate where RowNumber >1
(3 row(s) affected). its mean deleted
Now No duplicate row found ...
WITH tblDuplicate as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name ORDER BY Name)
As RowNumber,* FROM student1
)
SELECT * FROM tblDuplicate
RowNumber id name age
------------- ------ -------------------------
1 19 Ramu 29
1 7 Ramu Kumar 30
1 18 Sachi 28
1 14 Sachi Supriya 25
No comments:
Post a Comment