SQL: Find duplicated values associated with one ID

问题:

Thanks to NoDisplayName (SQL: Query to set value based on group parameter) for getting my table to have Primary tags. But now I need help with a query to find errors in my table.

Sample Input:

Column1 | Column2 | 
ID1       Primary         
ID1       Primary               
ID1                     
ID2       Primary
ID2       
ID3       Primary   
ID3       

Specifically what would a query to find if there is more than 1 Primary in Column2 associated with the same value in Column1?

I just need the output to be something actionable so I can then remove the duplicated Primary tags.

Thank you!

回答1:

select 
column1,
column2,
count(column1)
from tablename
groupby column1, column2
having count(column1) > 1

回答2:

Or you could do this:

;WITH CTE AS
(SELECT Column1, Column 2, ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY Column1) AS 'DupsWillHave2'
FROM Foo)
SELECT * 
FROM CTE
WHERE DupsWillHave2 > 1 

回答3:

I think the following is the query you want:

select column1, count(column1)
from tablename
where column2 = 'Primary'
group by column1
having count(*) > 1;

This will pick up only duplicate values of 'Primary'. It will not pick up duplicate blank values.