Thursday, January 8, 2009

How to delete the Duplicate Rows from SQL Server table

Lets see how to delete the duplicate Rows from SQL Server table.

Sample Table:

ID NAMES CODE
1 Mangal 101
1 Mangal 101
1 Mangal 101
2 Ricky 102
3 Brian 103
4 shiv 104
4 Shiv 104
5 Kunal 105
5 Kunal 105
6 Kate 106

As you can see in above table, record with Id 1 has come thrice, and ID 4 and 7 has come twice.

Desired Output: after removing duplicates -

ID NAMES CODE
1 Mangal 101
2 Ricky 102
3 Brian 103
4 Shiv 104
5 Kunal 105
6 Kate 106

Create And Load Sample Data -

-- Create Sample table
CREATE TABLE Temp
(
Id INT,
Names VARCHAR(100),
Code INT
)
GO
-- Load sample data
INSERT INTO Temp SELECT
1, 'Mangal', 123 UNION ALL SELECT
1, 'Mangal', 123 UNION ALL SELECT
1, 'Mangal', 123 UNION ALL SELECT
2, 'Ricky', 134  UNION ALL SELECT
3, 'Brian', 435 UNION ALL SELECT
4, 'Shiv', 223   UNION ALL SELECT
4, 'Shiv', 223   UNION ALL SELECT
5, 'Kunal', 654  UNION ALL SELECT
5, 'Kunal', 654  UNION ALL SELECT
6, 'Kate', 611
GO

Now first lets see how to find duplicate rows. With SQL Server 2005/2008 and Row_Number function, it has become very easy.


To Find Duplicate rows :

WITH Cte AS
(
SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq
FROM TEMP
)
SELECT DISTINCT ID, Names, Code
FROM Cte
WHERE Seq > 1

To Delete the Duplicate Rows :

WITH Cte AS
(
SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq
FROM TEMP
)
DELETE FROM cte
WHERE Seq > 1



- Mangal Pardeshi

1 comment:

  1. I am importing data from dbase

    i want to select on those records which added after 01/04/2012.

    select * from dbf_link...stock where Ship_Date>='04/01/2012'

    gives error.

    what is the correct syntax. Please advise


    ReplyDelete