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
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response