Remove Duplicate rows From SQL table

WITH common_table_expression

Create table
CREATE TABLE [dbo].[Employee](

[EID] [int] IDENTITY(1,1) NOT NULL,

[ENAME] [varchar](50) NULL,

[DEPT] [varchar](20) NULL,

[value] [int] NULL,

[type] [nchar](10) NULL
)

Add records table

INSERT INTO [dbo].[Employee]

([ENAME]

,[DEPT]

,[value]

,[type])

VALUES

('Ramu'

,'3'

,80

,'R')




GO
  
INSERT INTO [dbo].[Employee]

([ENAME]

,[DEPT]

,[value]

,[type])

VALUES

('Ramu'

,'3'

,80

,'R')




GO
  
INSERT INTO [dbo].[Employee]

([ENAME]

,[DEPT]

,[value]

,[type])

VALUES

('Ramu'

,'3'

,80

,'R')




GO

Check the results

select * from Employee

Remove Duplicate records

With [test2] AS

 

 

 

(

 

 

select RN=ROW_NUMBER() OVER( PARTITION BY ENAME ORDER by EID DESC),

ENAME,

 

 

EID

 

from Employee

 

 

 

)

 

Delete FROM [test2] where RN>1;

　
 select * from Employee