How to get rows that have sourctype 484456 only when group by GivenPartNumber_Non and vcompanyid ?

Question

I work on sql server 2014 i need to get rows that have source type 484456

when group by two columns group by GivenPartNumber_Non and vcompanyid

so i need to make select query display every group of rows by GivenPartNumber_Non and vcompanyid have source type 484456 only may be this group one row or 2rows or 3 rows or more etc any way i need to retrieve it .

create table #notmappedsources  
(  
GivenPartNumber_Non varchar(200),  
vcompanyid  int,  
SourceType int  
)  
insert into #notmappedsources(GivenPartNumber_Non,vcompanyid,SourceType)  
values  
('ADFGH22',1233,484456),  
('ADFGH22',1233,484456),  
('ADFGH22',1233,484456),  
  
('XFDY990',5489,484456),  
('XFDY990',5489,484456),  
  
('GX84322',2549,484456),  
  
  
  
  
('LKHG23',3201,484320),  
('LKHG23',3201,484320),  
('LKHG23',3201,484320),  
  
('DGHJ66',7041,484320),  
  
('FDYH36',8901,484320),  
('FDYH36',8901,484320),  
  
('MNH32',5601,489561),  
('MNH32',5601,489561),  
('MNH32',5601,484456),  
  
('NUI34',9076,489561),  
  
('KLMD33',5022,489561),  
('KLMD33',5022,484456)  

expected result as below :

Answer 1

I tried these 3 solutions second being the winner. And the third is the same as second but using ALL keyword.

-- Solution 1
 ;WITH correctInfo AS (SELECT GivenPartNumber_non, vcompanyID FROM #notmappedsources
 GROUP BY GivenPartNumber_Non, vcompanyid
 HAVING MIN(sourceType) = 484456 AND MAX(SourceType) = 484456)

 SELECT n.* FROM #notmappedsources n INNER JOIN correctInfo c
 ON c.vcompanyid = n.vcompanyid AND n.GivenPartNumber_Non = c.givenPartNumber_non;

-------- Solution 2
SELECT n.* FROM #notmappedsources n WHERE NOT EXISTS (SELECT 1 FROM #notmappedsources n2 WHERE n2.GivenPartNumber_Non = n.GivenPartNumber_Non
AND n2.vcompanyid = n.vcompanyid AND n2.SourceType<>484456)

-------- Solution 3
SELECT n.* FROM #notmappedsources n WHERE 484456 = all (SELECT sourceType FROM #notmappedsources n2 WHERE n2.GivenPartNumber_Non = n.GivenPartNumber_Non
AND n2.vcompanyid = n.vcompanyid)

Answer 2

Hi @ahmed salah
Please check this query:

;WITH CTE AS  
(  
 SELECT *,RANK()OVER(PARTITION BY GivenPartNumber_Non,vcompanyid ORDER BY SourceType) Rank_ASC  
         ,RANK()OVER(PARTITION BY GivenPartNumber_Non,vcompanyid ORDER BY SourceType DESC) Rank_DESC  
 FROM #notmappedsources  
)SELECT GivenPartNumber_Non, vcompanyid,SourceType  
 FROM CTE   
 WHERE SourceType = 484456 AND Rank_ASC=Rank_DESC  

Best regards,
LiHong

---

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.