Bobski Bobski - 2 months ago 5x
SQL Question

SQL Server 2008 select query difficulty

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
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
, but different

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

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.


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.