How to query DUPLICATE RECORDS from table?

• Leo-696352

  SSCrazy

  Points: 2708

  More actions

  October 29, 2008 at 1:21 pm

  #128223

  Hi,

  I have a table called EMPLOYEE which includes the following fields -

  EmpNo EmpFName EmpLName Dept Section Phone

  001 David Moore Office A 1234

  002 Jhon Davies Kitchen A 2345

  001 David Moore Office A 3456

  003 Sarah White Office A 6789

  How can I query the duplicate records from EMPLOYEE table?

  I want the result in

  EmpNo EmpFName EmpLName Dept Section Duplicate

  001 David Moore Office A 2

  I don't care about the Phone number is difference.

  Anybody know how to write a query?

  Thanks.

• Jerry Hung

  SSChampion

  Points: 12968

  More actions

  October 29, 2008 at 2:00 pm

  #891469

  GROUP BY?

  in SQL 2005 and above, ROW_NUMBER can be used as well

  SELECT

  EmpNo

  ,EmpFName

  ,EmpLName

  ,Dept

  ,Section

  ,Duplicate = COUNT(1)

  FROM Employee

  HAVING COUNT(1) > 1 -- to only show duplicates. Comment this out will show everybody

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005

• Leo-696352

  SSCrazy

  Points: 2708

  More actions

  October 29, 2008 at 2:48 pm

  #891493

  Thanks Jerry.