There can be multiple account names assigned to 1 account number. Since there are a million of rows in the DB I want to find out how I can query the account # that is only assigned to 1 account. Please see sample data below: Account # 100, 100, 500, 650, 250, 250, 600, 400, 400 Account Name ABA, DSA, ABA, DSA, ABA, DSA, DSA, ABA, ABA The result of the query should be account # 500,650,400 with ABA, DSA and ABA as account names because these account # only have one account name assigned to it. Account 100 and 250 have multiple account names assigned to it. How do I filter account number with only 1 account name assigned to it? Please help
You can try this: DECLARE @T1 TABLE ( AccountNum SMALLINT NOT NULL , AccountName CHAR(3) NOT NULL ) INSERT INTO @T1 SELECT 100 , 'ABA' UNION ALL SELECT 100 , 'DSA' UNION ALL SELECT 500 , 'ABA' UNION ALL SELECT 650 , 'DSA' UNION ALL SELECT 250 , 'ABA' UNION ALL SELECT 250 , 'DSA' UNION ALL SELECT 600 , 'DSA' UNION ALL SELECT 400 , 'ABA' UNION ALL SELECT 400 , 'ABA'; WITH cteNoDups AS ( SELECT t.AccountNum , t.AccountName FROM @T1 AS t GROUP BY t.AccountNum , t.AccountName ), cteCount AS ( SELECT a.AccountNum , COUNT(*) AS Cnt FROM cteNoDups a GROUP BY a.AccountNum HAVING COUNT(*) = 1 ) SELECT c.AccountNum , c.AccountName FROM cteCount AS b INNER JOIN cteNoDups AS c ON c.AccountNum = b.AccountNum; You have 600 with one name assigned to it as well. I hope I understood your problem correctly! Let me know if I missed something, I'll adjust my answer.
If you need to select only those accounts which have one account name assigned and no more than one then you can group by account number restricting the results to only include rows which have 1 account name: select a.* from YourTable a inner join ( select AccountNumber from YourTable group by AccountNumber having count(1) = 1 ) b on a.AccountNumber = b.AccountNumber; Please modify the query below changing the table and column names. Oleg