How to write T-SQL to extract duplicate composite key

Question

Hi All,

Lets say I have data with a composite key of CHDRNO,RSKNO. As per shown in example below, how can I write T-SQL code to extract the duplicate composite key of entire row from the entire table? Need help from expert on this because I need to extract the rows that consist of duplicate composite key for one of my company project.

   -- DDL and sample data population, start
   DECLARE @tbl TABLE (CHDRNO varchar(12), RSKNO int, ENDNLNE varchar(60),DATIME datetime2(7) )
   INSERT INTO @tbl (CHDRNO, RSKNO, ENDNLNE,DATIME) VALUES
   ('Z0012725','1','JUST','2020-08-03 14:45:02.7549260'),
   ('Z0012725','1','ABC','2020-08-04 14:45:02.7549260'),
   ('Z0012725','2','DEF','2020-08-03 14:45:02.7549260'),
   ('Z0012725','3','AND','2020-08-03 14:45:02.7549260')
   -- DDL and sample data population, end

Example and SQL Version

Answer 1

--Or

select CHDRNO, RSKNO, ENDNLNE,DATIME
from @tbl t
Where exists (select CHDRNO, RSKNO  from @tbl t1 
where t.CHDRNO=t1.CHDRNO and t.RSKNO=t1.RSKNO
group by  CHDRNO, RSKNO
having (count(*)>1 )
)

Answer 2

Here you are:

   -- DDL and sample data population, start
   DECLARE @tbl TABLE (CHDRNO varchar(12), RSKNO int, ENDNLNE varchar(60),DATIME datetime2(7) )
   INSERT INTO @tbl (CHDRNO, RSKNO, ENDNLNE,DATIME) VALUES
   ('Z0012725','1','JUST','2020-08-03 14:45:02.7549260'),
   ('Z0012725','1','ABC','2020-08-04 14:45:02.7549260'),
   ('Z0012725','2','DEF','2020-08-03 14:45:02.7549260'),
   ('Z0012725','3','AND','2020-08-03 14:45:02.7549260')

;with mycte as (
select *,count(*) over(partition by  CHDRNO,RSKNO) cnt from 
@tbl) 

select CHDRNO, RSKNO, ENDNLNE,DATIME from mycte
where cnt>1
 

Answer 3

Hi @jn93

Try this query:

SELECT A.* 
FROM @tbl A JOIN (SELECT CHDRNO,RSKNO FROM @tbl GROUP BY CHDRNO,RSKNO HAVING COUNT(CHDRNO)>1)B 
  ON A.CHDRNO=B.CHDRNO AND A.RSKNO=B.RSKNO

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.