How to Remove duplicate data From table in sqlserver table ?

Posted by Nandkishorre under Sql Server on 5/14/2013 | Points: 10 | Views : 1593 | Status : [Member] | Replies : 8

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

How to Remove duplicate data From table in sqlserver table

My table Data :
ID Name
344B6794 Visal
CA6B5B10 Raghu
9DC012F7 Visal
FDC12AC1 Raghu
9B439B6F Rakesh
F9F7D96A Naresh
A28909C3 Rakesh

I need to get below data only

ID Name
344B6794 Visal
CA6B5B10 Raghu
9B439B6F Rakesh
F9F7D96A Naresh

How can i get the data...?
Could any one know about this Reply me ?

Regards
Kishore

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: manoranjanguptaoutlook-19221 on: 5/14/2013 [Member] Starter | Points: 25

0	Hello This way u can Delete Duplicate data from table. :) First of all create table 1>create table tblmanu(Id int, Name varchar(50)); === 2>insert into tblmanu values(1,'Manu'); 2>insert into tblmanu values(2,'Jhon'); 3>insert into tblmanu values(3,'Manu'); ========== select * from tblmanu; ======= find the number of duplicate data:::: select name, count() Totalcount from tblmanu group by name having count() >1 order by count() desc; ================== delete from tblmanu where id not in(select max(id) from tblmanu group by name); duplicate data will delete =============== select from tblmanu; Manoranjan Gupta Manu 9873306227 Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Hello This way u can Delete Duplicate data from table. :)
First of all create table
1>create table tblmanu(Id int, Name varchar(50));
===
2>insert into tblmanu values(1,'Manu');
2>insert into tblmanu values(2,'Jhon');
3>insert into tblmanu values(3,'Manu');
==========
select * from tblmanu;

=======
find the number of duplicate data::::
select name, count(*) Totalcount from tblmanu group by name having count(*) >1 order by count(*) desc;
==================
delete from tblmanu where id not in(select max(id) from tblmanu group by name);
duplicate data will delete
===============
select * from tblmanu;

Manoranjan Gupta Manu
9873306227

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 5/14/2013 [Member] [MVP] Silver | Points: 25

0	Try SELECT A.id, A.name, t.name AS Expr1 FROM (SELECT MIN(id) AS id, name FROM mytab GROUP BY name) AS A INNER JOIN mytab AS t ON t.id = A.id If this post helps you mark it as answer Thanks Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Satyapriyanayak on: 5/14/2013 [Member] [MVP] Silver | Points: 25

0	Refer http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/ http://www.techrepublic.com/blog/datacenter/removing-duplicate-records-using-sql-server-2005/420 If this post helps you mark it as answer Thanks Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: aswinialuri-19361 on: 5/15/2013 [Member] Starter | Points: 25

0	HI I hope it will help you you have id and Name then you will add auto id to particular table like this alter table table name add autoid int identity(1,1) after that select * from Tablename where autoid not in (select max(autoid)_ from table name group by ID ,Name ) if you need first row as duplicate then use min or else last row max Mark AS answer if it helps you Thanks &Regards Mark as Answer if it helps you Thanks&Regards Aswini Aluri Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

HI
I hope it will help you

you have id and Name
then you will add auto id to particular table like this
alter table table name add autoid int identity(1,1)
after that
select * from Tablename where autoid not in (select max(autoid)_
from table name group by ID ,Name )
if you need first row as duplicate then use min or else last row max
Mark AS answer if it helps you
Thanks &Regards

Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 5/15/2013 [Member] Starter | Points: 25

0	Hi Nandkishorre, To delete duplicate rows based on name column ;with dup as ( select ,ROW_NUMBER()over(partition by name order by name)DupNo from TableName )delete from dup where DupNo>1 Nandkishorre, if this helps please login to Mark As Answer*. \| Alert Moderator

Posted by: Saranya Boopathi on: 5/23/2013 [Member] Starter | Points: 25

0	Hi, Try this create table tblmanu(Id varchar(50), Name varchar(50)); insert into tblmanu values('344B6794','Visal') insert into tblmanu values('CA6B5B10','Raghu') insert into tblmanu values('9DC012F7','Visal') insert into tblmanu values('FDC12AC1','Raghu') insert into tblmanu values('9B439B6F','Rakesh') insert into tblmanu values('F9F7D96A','Naresh') insert into tblmanu values('A28909C3','Rakesh') WITH tempTable as( SELECT ROW_NUMBER() Over(PARTITION BY Name ORDER BY Id) As RowNumber,* FROM tblmanu ) DELETE FROM tempTable where RowNumber >1 Saranya Boopathi Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

Try this

create table tblmanu(Id varchar(50), Name varchar(50));

insert into tblmanu values('344B6794','Visal')

insert into tblmanu values('CA6B5B10','Raghu')

insert into tblmanu values('9DC012F7','Visal')

insert into tblmanu values('FDC12AC1','Raghu')

insert into tblmanu values('9B439B6F','Rakesh')

insert into tblmanu values('F9F7D96A','Naresh')

insert into tblmanu values('A28909C3','Rakesh')

WITH tempTable as(

SELECT ROW_NUMBER() Over(PARTITION BY Name ORDER BY Id) As RowNumber,* FROM tblmanu

)

DELETE FROM tempTable where RowNumber >1

Saranya Boopathi

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Annaharris on: 5/24/2013 [Member] Starter | Points: 25

0	Thanks for sharing the query for removing the duplicate entries from the table. Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Nandkishorre on: 5/24/2013 [Member] Starter | Points: 25

0	I got answer.its working. select ID,Name from Table1 as A where ID = ( select top1 ID from Table1 where Name= A.Name) Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts