CODEWITHSUNDEEP
sql-server
Srinivasan
Srinivasan

Reputation: 12060

How to get duplicate record in sqlserver

I have data like below,

Document#                Name
__________________________________
Doc1                     John
Doc1                     John
Doc1                     Peter

I need to check whether any document is related with different Person. For eg., in our case doc1 is referenced for both the person, John and Peter. How to get this?

Upvotes: 0

Views: 47

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37510

Another approach would be to use window function, slightly different approach, but gives you more information if you need it:

select * from (
    select document#,
           name,
           count(distinct name) over (partition by document#) [cnt]
    from MyTable
) [a] where cnt > 1

Upvotes: 0

B3S
B3S

Reputation: 1051

Take a look to Count (Distinct) + Having clause combination

SELECT [Document], COUNT(DISTINCT [Name]) [Different names]
FROM MyTable
GROUP BY [Document]
HAVING COUNT(DISTINCT [Name]) > 1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522787

Try aggregating by document and then checking the distinct count of names:

SELECT [Document#]
FROM yourTable
GROUP BY [Document#]
HAVING COUNT(DISTINCT Name) > 1;

Another approach using a self join:

SELECT DISTINCT t1.[Document#]
FROM yourTable t1
INNER JOIN yourTable t2
    ON t1.Name <> t2.Name;

Upvotes: 1

Related Questions