January 27, 2014 at 12:17 am
declare @a1 table
(id int not null identity(1,1),
phone decimal(18,0),
adress nvarchar(100))
insert @a1
(phone,adress)
values
(111,'new york')
insert @a1
(phone,adress)
values
(111,'new york')
insert @a1
(phone,adress)
values
(111,'new york')
insert @a1
(phone,adress)
values
(222,'maxico')
insert @a1
(phone,adress)
values
(222,'mexico')
select*from @a1
id phone adress
----------- --------------------------------------- ----------------------------------------------------------------------------------------------------
1 111 new york
2 111 new york
3 111 new york
4 222 maxico
5 222 mexico
select phone,count(phone) as say from @a1 group by phone having count(phone)>1
phone say
--------------------------------------- -----------
111 3
222 2
how can I remove duplicate phone
for exmaple
after delete
select*from @a1
select*from @a1
id phone adress
----------- --------------------------------------- ----------------------------------------------------------------------------------------------------
1 111 new york
4 222 maxico
I wrote this to show for example
in my real table have 50000 rows
and
1751 dublicate rows
January 27, 2014 at 12:27 am
Did you try any googling at all? There are thousands of code samples on the internet on how to delete duplicates.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 27, 2014 at 1:17 am
You can get the maximum (or minimum) ID per phone number from your table and then delete any record that its ID is not in those IDs.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 29, 2014 at 9:05 am
As Adi said:
delete a
from @a1 a
left join (select phone,min(id) id from @a1 group by phone) d on a.id=d.id
where d.phone is null
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply