How to delete duplicate rows in sql server?

First we have to create a table as follows.

Create table mycompany1
(
     myID int,
     myFirstName nvarchar(100),
     myLastName nvarchar(100),
     mySalary int
)

GO

Insert into mycompany1 values (1, 'sai', 'reddy',  200000)
Insert into mycompany1 values (1, 'sai', 'reddy',  200000)
Insert into mycompany1 values (1, 'sai', 'reddy', 200000)
Insert into mycompany1 values (2, 'vijendhar', 'reddy', 100000)
Insert into mycompany1 values (2, 'vijendhar', 'reddy',  100000)
Insert into mycompany1 values (3, 'kalyan', 'varkala',  50000)
Insert into mycompany1 values (3, 'kalyan', 'varkala',  50000)
Insert into mycompany1 values (3, 'kalyan', 'varkala',  50000)

Now select the data from the table as follows.

select * from mycompany1

Now write a query as follows to remove duplicates.


with CTEdeleteduplicates as
(
select *, ROW_NUMBER() over(partition by myid order by myid) as rownumber
from mycompany1
  
)


delete from CTEdeleteduplicates where rownumber>1

Now the result will be as follows.