May 29, 2012 at 12:25 pm
-- 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
*/
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply