Get group of records based on condition (count is zero) in SQL Server

Hi venkatg,

Refer below query.

SQL

CREATE TABLE #Rarity(Rarity INT,IsDeleted INT,Id INT)
INSERT INTO #Rarity VALUES(1392,0,16839)
INSERT INTO #Rarity VALUES(1392,1,16840)
INSERT INTO #Rarity VALUES(1392,0,16841)
INSERT INTO #Rarity VALUES(1393,1,16842)
INSERT INTO #Rarity VALUES(1393,1,16843)
INSERT INTO #Rarity VALUES(1393,1,16844)

SELECT * 
FROM #Rarity 
WHERE Rarity = 
(
	SELECT Rarity
	FROM #Rarity 
	GROUP BY Rarity
	HAVING COUNT(CASE WHEN IsDeleted = 0 THEN 1 END) = 0
)

DROP TABLE #Rarity

Output

Rarity IsDeleted Id

1393     1             16842

1393     1             16843

1393     1             16844