Mysql Remove Duplicate keep 1st or keep last

 create table MyEmployee(

id int auto_increment primary key,

name varchar(50),

city varchar(50),

deptId int,

salary float,

dateOfJoining datetime

)

truncate table MyEmployee;

insert into MyEmployee(name,city,deptId,salary,dateOfJoining)

values('sagar','malvan',1,5000,now());

insert into MyEmployee(name,city,deptId,salary,dateOfJoining)

values('sangram','mumbai',1,10000,now());

insert into MyEmployee(name,city,deptId,salary,dateOfJoining)

values('sachin','banglore',2,15000,now());

insert into MyEmployee(name,city,deptId,salary,dateOfJoining)

values('vivek','mumbai',3,8000,now());

insert into MyEmployee(name,city,deptId,salary,dateOfJoining)

values('vishal','pune',4,6000,now());

insert into MyEmployee(name,city,deptId,salary,dateOfJoining)

values('vijay','nagpur',1,9700,now());

insert into MyEmployee(name,city,deptId,salary,dateOfJoining)

values('sagar','kankavali',2,6000,now());

insert into MyEmployee(name,city,deptId,salary,dateOfJoining)

values('sangram','pune',3,10000,now());

insert into MyEmployee(name,city,deptId,salary,dateOfJoining)

values('sachin','hyderabad',2,15000,now());

select * from MyEmployee;

Find Duplicate:

with duplicate as (

SELECT *,

ROW_NUMBER() OVER(PARTITION BY name) AS row_num

FROM MyEmployee

)

select * from duplicate where row_num > 1

Delete Duplicate keep first one:

with duplicate as (

SELECT *,

ROW_NUMBER() OVER(PARTITION BY name) AS row_num

FROM MyEmployee

)

delete MyEmployee from MyEmployee inner join duplicate on MyEmployee.id = duplicate.id

where duplicate.row_num > 1

Delete Duplicate keep last one

delete MyEmployee from MyEmployee join

(

SELECT min(id) minval ,max(id) maxval ,name from MyEmployee group by name

)t

where MyEmployee.name = t.name and MyEmployee.id !=t.maxval