Finding duplicate rows in SQL Server

Question

I have a SQL Server data set of associations, and there are many copy columns. I need to run a select explanation to get these and how much tricks, yet in addition return the ids that are related with every association.

A statement like:

SELECT     orgName, COUNT(*) AS dupes  
FROM         organizations  
GROUP BY orgName  
HAVING      (COUNT(*) > 1)

Will return something like

orgName        | dupes  
ABC Corp       | 7  
Foo Federation | 5  
Widget Company | 2 

But I'd also like to grab the IDs of them. Is there any way to do this? Maybe like a

orgName        | dupeCount | id  
ABC Corp       | 1         | 34  
ABC Corp       | 2         | 5  
...  
Widget Company | 1         | 10  
Widget Company | 2         | 2  

The explanation is that there is additionally a different table of clients that connect to these associations, and I might want to bring together them (in this way eliminate hoodwinks so the clients connect to a similar association rather than trick organizations). Yet, I would like part physically so I screw nothing up, however, I would in any case require an assertion returning the IDs of all the trick organizations so I can go through the rundown of clients. Can someone please help me with this?

Answer 1

Use this :

select o.orgName, oc.dupeCount, o.id
from organizations o
inner join (
    SELECT orgName, COUNT(*) AS dupeCount
    FROM organizations
    GROUP BY orgName
    HAVING COUNT(*) > 1
) oc on o.orgName = oc.orgName

I hope this helps you.

Unlock the power of data and embark on a journey towards becoming a skilled data scientist. Join our comprehensive Data Science Training program today!