select 'ganesh' name,9177272664 mobile
union all
select 'Siva' , 8686563686
union all
select 'mohan', 9347475971
union all
select 'Gowtham', 9966710081
union all
select 'siva', 9985658617
Here name is duplicates
To delete those details we use partition keyword
Partition Keyword gives the result as row numbers which are having common data in partition columns
for example "partition by name"
then result will be 1 siva , 2 siva ,1 ganesh etc.... like that
If we give " partition by name,mobile "
there is no duplicates so it gives all IDs as 1.
;with x as
(
select row_number() over(partition by no order by no) ID,* from tablename
)
delete from x where ID>1
Now it will delete duplicate record which is having ID as more than 1