Return id of row that has duplicate data

Question

I am needing to get the row id of rows which have duplicate

Select Name from table1 group by Name having count(1) > 1

table1

ID | Name    | ClientID
----------------------------
01 | John    |  01
02 | Sam     |  01
03 | Sue     |  01
04 | John    |  02
05 | John    |  01

the only problem is that it will only return the Name and not the ID of the column as I have multiple clients on the same table and I don't want to count the names from the other clients as duplicates.

So is there a way to find duplicate data in a table and return the ids of the rows so that I can then use those id's in another query?

Answer I went with modified

Thank you every one for the answers I went with this one which is a modification of the one I marked as the answer.

select t1.*
from (select count(*) over (partition by entityname) as cnt ,t1.*
      from table1 t1 where ClientID = 1
     ) t1
where cnt > 1 and ClientID = 1 order by cnt;

Gordon Linoff · Accepted Answer · 2016-09-26T11:37:40.503

3

Use a window function:

select t1.*
from (select t1.*, count(*) over (partition by name) as cnt
      from table1 t1
     ) t1
where cnt > 1;

The count(*) over (partition by name) counts the number of rows for each name. However, it does this by appending the count on each row, not by reducing the number of rows. That's the information you need for selecting the rows.

edited Sep 26 '16 at 11:37

answered Sep 26 '16 at 01:31

Gordon Linoff

1,242,037
58
646
786

Wow. ok so can you explain it for me? this looks like what I am wanting to use but I can't quite understand what is going on there. Thank you. – Xaedblade Sep 26 '16 at 03:47

score 1 · Answer 2 · answered Sep 26 '16 at 01:35

1

Select Name, min(ID) ROWID From table1 Group BY Name Having Count(ID)>1

min(ID) here will return the first time the ID appears in the duplicate, and the Count(ID)> 1 will filter out the rows where you have duplicates.

Good Luck!

answered Sep 26 '16 at 01:35

Krishna Chavali

11
2

So this will give me one id of every duplicate? – Xaedblade Sep 26 '16 at 03:44
It would tell you the first time a Name appears on the list. The query will return one line for each name, and the row id column will contain the ID where the ID appears on the original file. – Krishna Chavali Sep 26 '16 at 14:54
Great! That is exactly what I was looking for. Thank you – Xaedblade Sep 26 '16 at 20:49

score 0 · Answer 3 · answered Sep 26 '16 at 01:36

To see the link of dupes

Declare @Yourtable table (ID varchar(25),Name varchar(50),Client_ID varchar(25)) 
Insert into @Yourtable values 
('01','John','01'),
('02','Sam' ,'01'),
('03','Sue' ,'01'),
('04','John','02'),
('05','John','01')

Select A.*
      ,B.Dupes 
 From  @YourTable A
 Cross Apply (Select Dupes=(Select Stuff((Select Distinct ',' + cast(ID as varchar(25)) 
                              From @YourTable 
                              Where ID<>A.ID and Name=A.Name
                              For XML Path ('')),1,1,'')
                            )
              ) B
 Where Dupes is not null

Returns

ID  Name    Client_ID   Dupes
01  John    01          04,05
04  John    02          01,05
05  John    01          01,04

score 0 · Answer 4 · answered Sep 26 '16 at 02:41

0

You can query like this

;WITH cte_duplicates
AS (SELECT
  id, name, client_id,
  ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rc
FROM @Yourtable)
SELECT
  id, name, client_id
FROM cte_duplicates
WHERE rc > 1

answered Sep 26 '16 at 02:41

Kannan Kandasamy

13,405
3
25
38

score 0 · Answer 5 · answered Sep 26 '16 at 02:54

0

If you wanted to fileter out the duplicates based on both the name and ClientId, use the below query.

 ; with cte_1
   as (select *, count(*) over (partition by name,client_id order by ID) as dups
  from table1  ) 
 Select *
 From cte_1
 where dups> 1;

answered Sep 26 '16 at 02:54

Unnikrishnan R

4,965
1
12
21

Return id of row that has duplicate data

5 Answers5

Linked