//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