Need T-SQL Query to get duplicate values for same group set of values

Question

Hi All,

I have a query result set with 4 columns - ID, TypeId, TypeName , CreateDate

My source query result looks in below format.

ID TypeID TypeName CreateDate
1 1 Type-A 01/01/2002
2 1 Type-B 01/10/2002
3 1 Type-B 01/15/2002
4 1 Type-C 01/16/2002
5 2 Type-A 01/20/2002
6 2 Type-A 01/25/2004
7 2 Type-B 01/25/2004
8 2 Type-C 05/29/2004
9 3 Type-A 01/25/2003
10 3 Type-C 03/10/2004
11 3 Type-C 07/21/2004
12 4 Type-A 01/25/2003
13 4 Type-B 03/10/2004
14 4 Type-C 07/21/2004
15 5 Type-A 07/25/2006
16 5 Type-B 02/10/2007
17 6 Type-A 09/15/2014
18 6 Type-A 11/10/2014
19 6 Type-A 01/21/2015

Below are the rules to get required rows.

From TypeId = 1, need ID = 2, 3 rows because TypeName values - "B" are same, and skip ID=1,4
From TypeId = 2, need ID = 5, 6 rows because TypeName values - "A" are same, and skip ID=7,8
From TypeId = 3, need ID = 10,11 rows because TypeName values - "C" are same, and skip ID=9
From TypeId = 4, need 0 rows because TypeName values are unique.
From TypeId = 5, need 0 rows because TypeName values are unique.
From TypeId = 6, need ID = 17,18,19 (all 3) rows because TypeName values - "A" are same

Output result should be in below format.

ID TypeID TypeName CreateDate
2 1 Type-B 01/10/2002
3 1 Type-B 01/15/2002
5 2 Type-A 01/20/2002
6 2 Type-A 01/25/2004
10 3 Type-C 03/10/2004
11 3 Type-C 07/21/2004
6 2 Type-A 01/25/2004
10 3 Type-C 03/10/2004
11 3 Type-C 07/21/2004
17 6 Type-A 09/15/2014
18 6 Type-A 11/10/2014
19 6 Type-A 01/21/2015

Create Table #MySample ( ID int , TypeID int , TypeName varchar(10), CreateDate date)

insert into #MySample Values
(1, 1 , 'Type-A' , '01/01/2002'),
(2 , 1 , 'Type-B' , '01/10/2002'),
(3 , 1 , 'Type-B' , '01/15/2002'),
(4 , 1 , 'Type-C' , '01/16/2002'),
(5 , 2 , 'Type-A' , '01/20/2002'),
(6 , 2 , 'Type-A' , '01/25/2004'),
(7 , 2 , 'Type-B' , '01/25/2004'),
(8 , 2 , 'Type-C' , '05/29/2004'),
(9 , 3 , 'Type-A' , '01/25/2003'),
(10 , 3 , 'Type-C' , '03/10/2004'),
(11 , 3 , 'Type-C' , '07/21/2004'),
(12 , 4 , 'Type-A' , '01/25/2003'),
(13 , 4 , 'Type-B' , '03/10/2004'),
(14 , 4 , 'Type-C' , '07/21/2004'),
(15 , 5 , 'Type-A' , '07/25/2006'),
(16 , 5 , 'Type-B' , '02/10/2007'),
(17 , 6 , 'Type-A' , '09/15/2014'),
(18 , 6 , 'Type-A' , '11/10/2014'),
(19 , 6 , 'Type-A' , '01/21/2015')

Thanks in advance for your help.
RH

Answer 1

WITH CTE_Duplicates AS  
(  
SELECT	   
		TypeID, 	  
		TypeName  
FROM	#MySample  
GROUP BY 		   
		TypeID,  
		TypeName  
HAVING	Count(1) > 1  
)  
  
SELECT	*   
FROM	#MySample S  
INNER JOIN CTE_Duplicates D  
ON		S.TypeID = D.TypeID  
AND		S.TypeName = D.TypeName  

Answer 2

Another way

;WITH CTE_Duplicates AS  
(  
SELECT       
        ID,  
	    TypeID,       
        TypeName,  
	    CreateDate,  
	    COUNT(*) OVER(PARTITION BY TypeID, TypeName) AS Cnt  
FROM    #MySample  
)  
  
SELECT   
    ID  
	TypeID,  
	TypeName,  
	CreateDate, cnt  
FROM CTE_Duplicates  
WHERE Cnt > 1;  

Answer 3

If you don't want to use cte, subquery is also a good choice：

SELECT   
     ID  
     TypeID,  
     TypeName,  
     CreateDate, cnt  
 FROM   
 (  
	 SELECT       
         ID,  
         TypeID,       
         TypeName,  
         CreateDate,  
         count(*) OVER(PARTITION BY TypeID, TypeName) AS Cnt  
 FROM    #MySample  
   
 )A  
 where cnt>1  

Bert Zhou