question

GC2014 avatar image
GC2014 asked

Identify unique values from 2 rows

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
sqlserver
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you dennis and oleg! i have encountered this problem when applying for a job. i got the job and im excited to try it out in their db.
0 Likes 0 ·
Congrats! Good luck on your new job.
0 Likes 0 ·
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
DenisT avatar image
DenisT answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.