Deepan's Tech

CREATE TABLE [DupEmployee]( id int identity(1,1), [Name] [varchar](50) NULL, [Age] [int] NULL, [Designation] [varchar](50) NULL,  )  insert into [DupEmployee] values('Raj',32,'TL')  insert into [DupEmployee] values('Deena',28,'SS')  insert into [DupEmployee] values('Raj',32,'TL')  insert into [DupEmployee] values('Gow',27,'TA')  insert into [DupEmployee] values('Raj',32,'TL')  insert into [DupEmployee] values('Siva',27,'TTL')  insert into [DupEmployee] values('Gow',27,'TA') DELETE FROM DupEmployee WHERE ID NOT IN ( SELECT MIN(id) FROM DupEmployee GROUP BY Name,Age,Designation) Result of select query is  2 4 1 6 We can do this using CTE also... ref http://deepanstech.blogspot.in/2017/04/sql-ctecommon-table-expressions.html

CREATE TABLE TBLTEST(ID INT,FIRSTNAME VARCHAR(20),LASTNAME VARCHAR(20)) INSERT INTO TBLTEST VALUES(1,'VANNI','RAJ') INSERT INTO TBLTEST VALUES(1,'VANNI','RAJ') INSERT INTO TBLTEST VALUES(2,'TEST','TEST') ;WITH CTE AS( SELECT  ID,FIRSTNAME,LASTNAME,  ROW_NUMBER() OVER(ORDER BY FIRSTNAME,LASTNAME) ROW_NUM,  RANK() OVER(ORDER BY FIRSTNAME,LASTNAME) RNK FROM  TBLTEST) DELETE FROM CTE WHERE ROW_NUM <> RNK; SELECT * fROM TBLTEST DROP TABLE  TBLTEST