// Creating table and inserting some records
create table email_list
(
name varchar(100),
email varchar(50)
)
go
insert into email_list values (‘himanshu’, ‘him@gmail.com’);
insert into email_list values (‘prem’, ‘prem@gmail.com’);
insert into email_list values (‘himanshu’, ‘him@gmail.com’);
insert into email_list values (‘prem’, ‘prem@gmail.com’);
insert into email_list values (‘himanshu’, ‘him@gmail.com’);
create table email_list
(
name varchar(100),
email varchar(50)
)
go
insert into email_list values (‘himanshu’, ‘him@gmail.com’);
insert into email_list values (‘prem’, ‘prem@gmail.com’);
insert into email_list values (‘himanshu’, ‘him@gmail.com’);
insert into email_list values (‘prem’, ‘prem@gmail.com’);
insert into email_list values (‘himanshu’, ‘him@gmail.com’);
// Showing records
select * from email_list
order by 1,2
// Deleting Duplicate rows
with list_duplicate( name, email, duplicate_count)as
(select name, email, ROW_NUMBER() over(PARTITION By name , email order by name, email) as duplicate_count from email_list)
delete from list_duplicate where duplicate_count>1