Return Id Of Row That Has Duplicate Data

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 ---------------------------

Solution 1:

Use a window function:

select t1.*from (select t1.*, count(*) over (partitionby 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.

Solution 2:

Select Name, min(ID) ROWID From table1 GroupBY Name HavingCount(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!

Solution 3:

To see the link of dupes

Declare@Yourtabletable (ID varchar(25),Name varchar(50),Client_ID varchar(25)) 
Insertinto@Yourtablevalues 
('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((SelectDistinct','+cast(ID asvarchar(25)) 
                              From@YourTableWhere ID<>A.ID and Name=A.Name
                              For XML Path ('')),1,1,'')
                            )
              ) B
 Where Dupes isnotnull

Returns

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

Solution 4:

You can query like this

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

Solution 5:

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 orderby ID) as dups
  from table1  ) 
 Select *
 From cte_1
 where dups> 1;