Display duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Display duplicate records

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-10 : 19:59:46

How to display all the duplicate records in a table with a specific id

eg:

emp_id phone contact
123 456456 hjghjg
123 78788 jhjjjj
678 689899 hjhjjj
678 4545 ghgfhfg
1679 5677 uuiuiyi

Here I want only the duplicate records to be displayed

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-10 : 20:13:24

Get a list of the ID's that are duplicates:

SELECT emp_id
  FROM MyTable
  GROUP BY emp_id
  HAVING COUNT(*) > 1

Next, this will display all the duplicates, including the "original"

SELECT emp_id, phone, contact
  FROM MyTable
  WHERE emp_id IN (insert the select above here)
  ORDER BY emp_id, phone, contact

Now the trick is to display the duplicates, without the originals...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources