Query to delete duplicate records in Sql

In Sql there is Common Table Expression (CTE) which is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query.


I used CTE to delete duplicate records in a table. The following script contains defining table, inserting data into table and then identifying duplicates.


CREATE TABLE [dbo].[Table1](
 [ID] [uniqueidentifier] NOT NULL,
 [SERNO] [varchar](25) NOT NULL,
 [StartDATE] [datetime] NULL,
 [EndDATE] [datetime] NULL
) ON [PRIMARY]

INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('DA41B9AB-BEAF-412C-9815-0C48E9018293','URR025979','Sep 27 2006 12:00:00','Sep 30 2011 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('DA41B9AB-BEAF-412C-9815-0C48E9018293','URR021379','Sep 27 2006 12:00:00','Sep 29 2011 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('57CF812D-A4A5-4521-9A0B-346208913424','WTM001540','Aug 26 2008 12:00:00','Aug 30 2012 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('57CF812D-A4A5-4521-9A0B-346208913424','WTM001540','Aug 26 2008 12:00:00','Aug 30 2012 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('5169908F-D524-4034-9690-38ED87790DC8','YHT651144','Nov  2 2010 12:00:00','Aug 30 2015 11:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('5169908F-D524-4034-9690-38ED87790DC8','YHT651144','Nov  2 2010 12:00:00','Aug 30 2015 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('5169908F-D524-4034-9690-38ED87790DC8','YHT651145','Nov  2 2010 12:00:00','Aug 30 2015 12:00:00')
INSERT INTO [Table1] ([ID],[SERNO],[StartDATE],[EndDATE])VALUES('5169908F-D524-4034-9690-38ED87790DC8','YHT651145','Nov  2 2010 12:00:00','Aug 30 2015 10:00:00')
WITH cte AS    
( SELECT ID, SERNO, StartDATE, EndDATE,        
  ROW_NUMBER() OVER (PARTITION BY ID, SERNO, StartDATE, EndDATE                           
 ORDER BY EndDATE DESC) AS sequence FROM Table1) 
SELECT ID, SERNO, StartDATE, EndDATE, sequence FROM cte WHERE sequence>=1

If you need to delete the duplicates, delete the records which have sequence >1
WITH cte AS    
( SELECT ID, SERNO, StartDATE, EndDATE,        
  ROW_NUMBER() OVER (PARTITION BY ID, SERNO, StartDATE, EndDATE                           
 ORDER BY EndDATE DESC) AS sequence FROM Table1) 
DELETE FROM cte WHERE sequence > 1