Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete Duplicates in SQL 2000, SQL 2005, SQL 2008 or higher Expand / Collapse
Author
Message
Posted Tuesday, May 29, 2012 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:21 PM
Points: 4, Visits: 43
-- SQL 2005, SQL 2008 and Higher
WITH XT(i, notes, RowNumber) AS
(
SELECT i, notes, ROW_NUMBER() OVER (PARTITION BY i, notes ORDER BY i, notes) AS RowNumber
FROM dbo.temp
)
DELETE FROM XT WHERE RowNumber > 1;
GO

-- SQL 2000 Script
DECLARE @DupCount INT
SET @DupCount = 0
SELECT @DupCount = COUNT(*)
FROM dbo.temp
GROUP BY i, notes
HAVING COUNT(*) > 1

WHILE @DupCount > 0
BEGIN
SET ROWCOUNT 1

DELETE dbo.temp
FROM dbo.temp t1
JOIN (SELECT i, notes
FROM dbo.temp
GROUP BY i, notes
HAVING COUNT(*) > 1
) t2
ON t1.i = t2.i
AND t1.notes = t2.notes

SET ROWCOUNT 0

SELECT @DupCount = COUNT(*)
FROM (SELECT *
FROM dbo.temp
GROUP BY i, notes
HAVING COUNT(*) > 1
) tp
END
GO


SELECT * FROM dbo.temp

/*
-- Sample Create Table/Insert Scripts for above queries
--DROP TABLE dbo.temp
CREATE TABLE dbo.temp(i int, notes varchar(100))
GO

INSERT INTO dbo.temp VALUES (1, 'Notes1')
INSERT INTO dbo.temp VALUES (1, 'Notes1')
INSERT INTO dbo.temp VALUES (1, 'Notes1')
INSERT INTO dbo.temp VALUES (2, 'Notes1')
INSERT INTO dbo.temp VALUES (3, 'Notes1')
INSERT INTO dbo.temp VALUES (1, 'Notes2')
INSERT INTO dbo.temp VALUES (2, 'Notes2')
INSERT INTO dbo.temp VALUES (2, 'Notes2')
INSERT INTO dbo.temp VALUES (2, 'Notes3')
INSERT INTO dbo.temp VALUES (4, 'Notes4')
INSERT INTO dbo.temp VALUES (4, 'Notes4')
GO
*/
Post #1307956
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse