Jin Yong Jin Yong - 4 years ago 131
SQL Question

How can I check duplicate record in SQL Server

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download