Thursday, April 21, 2011

Remove Duplicates in Microsoft Sql server

There are many ways to do that but most probably it would be much round trip , here is the simple way of removing the duplicates.

insert into santest values (1,'A')
insert into santest values (1,'A')
insert into santest values (1,'A')
insert into santest values (2,'B')
insert into santest values (2,'B')
insert into santest values (3,'C')
insert into santest values (3,'C')

WITH tbl_del AS (
SELECT *,ROW_NUMBER () OVER (
PARTITION BY id,name ORDER BY id ) AS Rnum
FROM santest)

DELETE FROM tbl_del WHERE Rnum > 1

Code Formater

Paste Here Your Source Code
Source Code Formatting Options
1) Convert Tab into Space :
2) Need Line Code Numbering :
3) Remove blank lines :
4) Embeded styles / Stylesheet :
5) Code Block Width :
6) Code Block Height :
7) Alternative Background :
Copy Formatted Source Code
 
Preview Of Formatted Code