Finding 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)

Finding duplicate records

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-07-08 : 21:53:01

I just can't figure it out. I have a table where the client name may appear more than once. What T-SQL query can I use to show only the records where the client name is found more than once in the table. Essentially, I need something like:

SELECT *
FROM MyTable
WHERE EXISTS
(SELECT FullName, COUNT(FullName) AS Cnt
FROM MyTable
GROUP BY FullName where Cnt > 1)

Here, it's obvious that the "where Cnt > 1" cannot be used in this context, so what can I do?

Thanks!

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-08 : 21:58:10

This should do it:

select * from mytable
where fullname in
(
SELECT fullname
from mytable
group by fullname
having count(1) > 1
)

WindChaser
Posting Yak Master

225 Posts

Posted - 2004-07-08 : 22:04:52

Haaa! Beautiful and simple. Thanks kselvia !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources