Rohit Kainth
How to find and remove duplicates from a table?
By Rohit Kainth in SQL Server on Oct 09 2018
  • Manish Maharjan
    May, 2019 27

    Table Name: Employee S.No Name Gender Salary 1 Ram Male 60000 1 Ram Male 60000 2 Gita Female 55000 3 Shyam Male 75000 1 Ram Male 60000 3 Shyam Male 75000 2 Gita Female 55000With EmployeeCTE AS ( select *, Row_Number() over (partition by Id Order by Id) As RowNumber from Employee) Delete from EmployeeCTE where RowNumber >1

    • 0
  • Rohit Kainth
    Oct, 2018 9

    We can remove duplicates using a Ranking function Row_Number() with partition by clause. To learn more about Windows Ranking Function, you can also visit CREATE TABLE TEST_WF(ID INT, NAME VARCHAR(5))INSERT INTO TEST_WF VALUES (1,'A'),(2,'B'),(3,'B'),(4,'C'),(5,'C'),(6,'C'),(7,'D')SELECT * INTO #TEMP FROM TEST_WF;WITH CTE AS (SELECT NAME, ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS RN FROM #TEMP) DELETE FROM CTE WHERE RN > 1

    • 0

Most Popular Job Functions