How To Delete Duplicate Rows In SQL?

Step 1:

Create database LearningPoint92;

Step 2:

use LearningPoint92;

step 3:

create table Emp (id int not null,name varchar(20),address varchar(24) not null,salary int not null);

step 4:

select * from Emp;

step 5:

insert into Emp(id,name,address,salary)values(101,'suryosnata','pune',35000);

insert into Emp(id,name,address,salary)values(101,'suryosnata','pune',35000);

insert into Emp(id,name,address,salary)values(101,'suryosnata','pune',35000);

insert into Emp(id,name,address,salary)values(102,'dipak','nigdi',45000);

insert into Emp(id,name,address,salary)values(102,'dipak','nigdi',45000);

insert into Emp(id,name,address,salary)values(102,'dipak','nigdi',45000);

step 6:

WITH emp1 as

(

select *,ROW_NUMBER() over (PARTITION by id order by id) as rownumber from Emp

)

Step 7:

delete from emp1 where rownumber>1

step 8:

select * from Emp;