How to get duplicate record in sqlserver

Question

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?

Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — ahmed abdelqader, May 29 '18 at 07:23

score 1 · Answer 1 · answered May 29 '18 at 07:27

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;

score 0 · Answer 2 · answered May 29 '18 at 07:27

0

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

answered May 29 '18 at 07:27

B3S

1,021
7
18

score 0 · Answer 3 · answered May 29 '18 at 07:51

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

How to get duplicate record in sqlserver

3 Answers3