How can I check duplicate record in SQL Server [duplicate]

Question

Does any one know how can I write a SQL Server script to check whether table is contain duplicate phone number?

Example:

I have a table called customer with following data

name   telephone
alvin  0396521254
alan   0396521425
amy    0396521425

How can I write a script in SQL Server that can return those records with duplicate telephone number??

Answer 1

To see values with duplicates:

  SELECT c.telephone
    FROM CUSTOMER c
GROUP BY c.telephone
  HAVING COUNT(*) > 1

To see related records in the table for those duplicates:

SELECT c.*
  FROM CUSTOMER c
  JOIN (SELECT c.telephone
          FROM CUSTOMER c
      GROUP BY c.telephone
        HAVING COUNT(*) > 1) x ON x.telephone = c.telephone