-2

I have SQL Server table with phone number as column name. Now I want to select only unique phone numbers.

The picture shows id ,company name, companyid, phone number. I want find only duplicate phone numbers not duplicate row. If duplicate cell value then delete the row. How can I do that?

Sample table shown here:

Table picture

mbdAli
  • 31
  • 9
  • I have a problem here, I am sorry , forgot to mention that, actually I want find duplicate in a cell (here phone number). i want delete the row if the phone number is exist regardless of company name. – mbdAli Sep 13 '15 at 15:17
  • Use this (use Min or Max. Your choice) Teste the select before : Delete TableName where Id in ( Select Min(Id) from TableName group by PhoneNumber having count(*) > 1 –  Sep 13 '15 at 15:19
  • the problem solved Thanks @Elizeu – mbdAli Sep 13 '15 at 15:27
  • Select the answer with useful to help the community. thanks! –  Sep 13 '15 at 15:35

2 Answers2

1

Use ROW_NUMBER()

;withe cte1 
as
(
select *,Row_Number() Over (PARTITION BY PhoneNumber ORDER BY Id) as rn from Table_Name
)
select * from cte1 where rn=1;
A_Sk
  • 4,532
  • 3
  • 27
  • 51
0

Use Having clause: Remenber to teste select before and choose between Min or max id you want continue exist. Maybe you need execute more than once

Delete TableName where Id in 
( Select Min(Id) from TableName 
where <create your filter here>
group by PhoneNumber having count(*) > 1 )

to help understand Having: "Having" Clause works lile a "where" applied to the "group by".