SQL Server 2008 select query difficulty

Question

I have a table with over 100k records. Here my issue, I have a bunch of columns

CompanyID CompanyName CompanyServiceID   ServiceTypeID    Active
----------------------------------------------------------------
1         Xerox       17                 33               Yes 
2         Microsoft   19                 39               Yes
3         Oracle      22                 54               Yes
2         Microsoft   19                 36               Yes

So here's how my table looks, it has about 30 other columns but they are irrelevant for this question.

Here's my quandary..I'm trying to select all records where CompanyID and CompanyServiceID are the same, so basically as you can see in the table above, I have Microsoft that appears twice in the table, and has the same CompanyID and CompanyServiceID, but different ServiceTypeID.

I need to be able to search all records where there are duplicates. The person maintaining this data was very messy and did not update some of the columns properly so I have to go through all the records and find where there are records that have the same CompanyID and CompanyServiceID.

Is there a generic query that would be able to do that?

None of these columns are my primary key, I have a column with record number that increments by 1.

Answer 1

You can try something like this:

   SELECT CompanyName, COUNT(CompanyServiceID) 
   FROM //table name here
   GROUP BY CompanyName
   HAVING ( COUNT(CompanyServiceID) > 1 )

This will return a grouped list of all companies with multiple entries. You can modify what columns you want in the SELECT statement if you need other info from the record as well.