Finding duplicate rows in SQL Server

0 votes

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?

Aug 13, 2022 in Data Science by Kithuzzz
• 38,010 points
335 views

1 answer to this question.

0 votes

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!

answered Aug 14, 2022 by narikkadan
• 63,420 points

Related Questions In Data Science

0 votes
0 answers

Introduction to Statistical Learning with Applications in R Figure Codes

I recently bought the following book: An Introduction ...READ MORE

Jun 1, 2022 in Data Science by avinash
• 1,840 points
369 views
0 votes
1 answer

Difference between the == and %in% operators in R

percent in percent "returns a vector of ...READ MORE

answered Jun 20, 2022 in Data Science by Sohail
• 3,040 points
437 views
0 votes
1 answer

Difference between the == and %in% operators in R

According to help('percent in percent'), percent in ...READ MORE

answered Jun 23, 2022 in Data Science by Sohail
• 3,040 points
229 views
0 votes
1 answer

How to make loop for one-at-a time logistic regression in R?

You're probably looking for something similar to ...READ MORE

answered Jun 20, 2022 in Data Science by Sohail
• 3,040 points
775 views
0 votes
1 answer

do-while loop in R

Unlike for and while loops, which test the loop condition at ...READ MORE

answered Jun 1, 2022 in Data Science by Sohail
• 3,040 points
431 views
0 votes
1 answer

What does c do in R?

The c function in R programming usually stands ...READ MORE

answered Jun 1, 2022 in Data Science by Sohail
• 3,040 points

edited Nov 28, 2023 by Soumya 20,885 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

CTEs and ROW_NUMBER can be combined together which will ...READ MORE

answered Feb 10, 2022 in Database by Vaani
• 7,020 points
586 views
0 votes
1 answer

How to duplicate IDs with Hive QL / Impala / Python

Based on your example, supposing id2 always ...READ MORE

answered Oct 1, 2018 in Python by Priyaj
• 58,090 points
742 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
581 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,020 points
591 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP