Need Help Finding Duplicate Field Data

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

General SQL Server Forums

New to SQL Server Programming

Need Help Finding Duplicate Field Data

Author

Topic

sqlnoob35
Starting Member

10 Posts

Posted - 2011-01-12 : 12:22:45

Hey guys,

I'm stuck on this one. I need to find all records in my table that have the following:

1. Two records per SSN
2. The agency name in both records is the same

Sample data:

SSN AGENCY

123457878 DHS
123457878 DHS

I know I can use the GROUP BY and Count(SSN) > 1 to find the duplicate SSNS, but it is pulling in records that have two SSNS and Different AGENCY names like this:

SSN AGENCY

123457878 DHS
123457878 NIC

How can I filter for only ones with the same agency name??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 12:31:50

[code]
SELECT SSN, AGENCY
FROM Table
GROUP BY SSN, AGENCY
HAVING COUNT(1) > 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnoob35
Starting Member

10 Posts

Posted - 2011-01-12 : 16:15:00

oh that was easy. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 10:51:26

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources