How to delete duplicate rows in a Table keeping only one using MSSQL Query?

Delete from table_name where field name not in (Select max(field name) from table_name group by field name) => incorrect T-SQL E.g: Items tbl has data as IdK IdP 1 1 1 2 2 1 2 1

by using row_number with seperated by argument and then deltete where you get row no. more than 1

delete t1 from Table t1 , t2 where t1.Namecol=t2.NameCol and t1.Id>t2.Id

-- deleting the duplicate rows form the table using CET with cet_deupdelete as ( select * ,ranking = DENSE_RANK() over (partition by empno,ename,job,mgr,hiredate,sal,comm,deptno order by newid() asc) from empdup ) select * from cet_deupdelete where ranking >1

Copy Paste this code and Run In a new SQL query Window &gt &gt CREATE TABLE [dbo].[Employee]( [ID] INT NOT NULL, [Email] [varchar](50) NOT NULL ); --drop table dbo.Employee INSERT INTO [dbo].[Employee] VALUES (1,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (2,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (3,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (4,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (5,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (6,'[email protected]'); INSERT INTO [dbo].[Employee] VALUES (7,'[email protected]'); --SELECT MAX(ID),EMAIL,ROW_NUMBER() OVER (ORDER BY EMAIL ASC) AS 'ROW' --FROM DBO.EMPLOYEE --GROUP BY EMAIL WITH T1 AS ( SELECT ID FROM DBO.EMPLOYEE EXCEPT SELECT MAX(ID) AS ID FROM DBO.EMPLOYEE GROUP BY Email HAVING COUNT(Email) &gt 1 ) DELETE FROM dbo.Employee WHERE ID IN (SELECT ID FROM T1) SELECT * FROM DBO.EMPLOYEE

Delete from table_name where field name not in (Select max(field name) from table_name group by field name)

Delete from table where id not in (Select max(id) from table group by id) This is the simple query to delete duplicate records from the table.

SELECT DISTINCT * INTO NewTable From OriginalTable TRUNCATE TABLE OriginalTable INSERT INTO OriginalTable SELECT * FROM NewTable DROP TABLE NewTable

Please once go through below link. http://www.dotnet-tricks.com/Tutorial/sqlserver/IL3S290812-Remove-duplicate-records-from-a-table-in-SQL-Server.html

WITH TempEmp (Emp_Name,Emp_Age,Emp_Salary,Emp_City,Dupli_Col) AS ( SELECT *,ROW_NUMBER() OVER(PARTITION by Emp_Name, Emp_Age,Emp_Salary,Emp_City ORDER BY Emp_Name) AS Dupli_Col FROM Employee_Detail ) /* DELETE Duplicate Data */ Delete from TempEmp where Dupli_Col>1 /* Select Query */

WITH TempEmp (Emp_Name,Emp_Age,Emp_Salary,Emp_City,Dupli_Col) AS ( SELECT *,ROW_NUMBER() OVER(PARTITION by Emp_Name, Emp_Age,Emp_Salary,Emp_City ORDER BY Emp_Name) AS Dupli_Col FROM Employee_Detail )

--- First create the table and insert duplicate values CREATE TABLE dbo.tblEmployee (ID INT IDENTITY, EmpID INT, Name VARCHAR(50)) INSERT INTO dbo.tblEmployee VALUES (1,'Name1') INSERT INTO dbo.tblEmployee VALUES (2,'Name2') INSERT INTO dbo.tblEmployee VALUES (1,'Name1') INSERT INTO dbo.tblEmployee VALUES (2,'Name2') INSERT INTO dbo.tblEmployee VALUES (3,'Name3') INSERT INTO dbo.tblEmployee VALUES (3,'Name3') --- Its the Quickest way to delete the Duplicetes --- The Below SQL Query will first fetch the nonunique records and then the Delete those DELETE FROM dbo.tblEmployee WHERE ID IN (SELECT ID FROM dbo.tblEmployee e LEFT OUTER JOIN (SELECT MAX(ID) AS DupID, EmpID FROM dbo.tblEmployee GROUP BY EmpID) eUnique ON e.ID = eUnique.DupID WHERE eUnique.DupID IS NULL)

CREATE TABLE san(ID INT) ---Add duplicate IDs --- execute below command to remove duplicate ; WITH tmp AS ( SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Id) AS row FROM san ) DELETE FROM tmp WHERE row!=1

WITH TempId AS ( SELECT *, row_number() OVER(PARTITION BY ID, FNAME,LNAME ORDER BY ID) AS [Num] FROM Employee) DELETE TempId WHERE [Num] > 1 Select * from Employee Still you can find the details in SQLFiddle here http://sqlfiddle.com/#!6/394a9/1