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.
| Author |
Topic |
|
dcronin
Starting Member
1 Post |
 Posted - 2006-01-20 : 12:23:32
|
| I am trying to find duplicate names in my entity table using the following code:select e1.entity_key, e2.entity_key, e1.namefrom entity e1, entity e2where e1.name=e2.nameand e1.entity_key!=e2.entity_keygroup by e1.entity_key, e2.entity_key, e1.nameorder by e1.entity_keyIt works fine for single duplicates as follows:entity1 key entity2 key entity1 name entity2 name4299 4297 NG, MARIE NG, MARIE4297 4299 NG, MARIE NG, MARIEIt's not so great for three or more as follows:entity1 key entity2 key entity1 name entity2 name4426 4420 PALOMINO, MARK PALOMINO, MARK4431 4420 PALOMINO, MARK PALOMINO, MARK4420 4426 PALOMINO, MARK PALOMINO, MARK4431 4426 PALOMINO, MARK PALOMINO, MARK4420 4431 PALOMINO, MARK PALOMINO, MARK4426 4431 PALOMINO, MARK PALOMINO, MARKHow can I modify the sql to eliminate the entity1 repeats in those cases? |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-01-20 : 12:36:23
|
| Something like this?SELECT e1.* FROM entity AS e1 JOIN (SELECT entity.name FROM entity GROUP BY entity.name HAVING COUNT(*) > 1) AS e2ON e1.name = e2.name |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
removing repeats for duplicates