InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 01:52:45 GMT20http://www.sqlservercentral.com/Forums/Topic1307956-543-1.aspx[b]-- SQL 2005, SQL 2008 and Higher[/b]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[b]-- SQL 2000 Script[/b]DECLARE @DupCount INTSET @DupCount = 0SELECT @DupCount = COUNT(*) FROM dbo.temp GROUP BY i, notes HAVING COUNT(*) > 1WHILE @DupCount > 0BEGIN 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 ) tpENDGOSELECT * FROM dbo.temp[i]/*-- Sample Create Table/Insert Scripts for above queries--DROP TABLE dbo.tempCREATE TABLE dbo.temp(i int, notes varchar(100))GOINSERT 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*/[/i]Tue, 29 May 2012 12:25:36 GMTBINU PRASAD.M