kldesigns
1/30/2020 - 1:10 AM

SQL Delete Duplicate Rows

//Note Change the 'DELETE' to 'SELECT' to see the records that will be deleted.
//Example 1
//https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/

WITH CTE([FirstName], 
    [LastName], 
    [Country], 
    DuplicateCount)
AS (SELECT [FirstName], 
           [LastName], 
           [Country], 
           ROW_NUMBER() OVER(PARTITION BY [FirstName], 
                                          [LastName], 
                                          [Country]
           ORDER BY ID) AS DuplicateCount
    FROM [SampleDB].[dbo].[Employee])
DELETE FROM CTE
WHERE DuplicateCount > 1;

//Example 2 - generic
//https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

//Example 3 applied

/****** Script for SelectTopNRows command from SSMS  
WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

******/
WITH CTE AS(

SELECT [Class]
      ,[Effective Date]
      ,[Description]
      ,[Normal Hours]
      ,[Hourly]
      ,[Weekly]
      ,[Fortnightly]
      ,[Monthly]
      ,[Annual]
      ,[Weeks per Year]
      ,[Minimum Annual Salar]
      ,[Maximum Overtime Rat]
      ,[Overtime Rule]
      ,[No Individual Allowa]
      ,[Currency]
      ,[Expiry Date]
      ,[Classification]
      ,[Age Rules Code]
      ,[Award Code]
      ,[Code]
      ,[Rate]
      ,[Next Class]
      ,[Previous Class]
      ,[Target Hours]
      ,[Grade for Award]
      ,[Years in Grade]
      ,[Years  in Class]
      ,[Months in Class]
      ,[Years of Service]
      ,[HDA Number of Workin]
	  ,RN = ROW_NUMBER()OVER(PARTITION BY [Class], [Effective Date] ORDER BY [Class])
  FROM [PeopleConnect_Data_Migration].[dbo].[PC-AWD 1]
  )
  DELETE FROM CTE WHERE RN > 1