SQL Puzzle | Ids with only value “One”

In this puzzle you need to fetch rows for the Ids where the value in the vals column is only “One”. For more details please check the sample input and expected output.

Sample Input

Id Nums Vals
A 141 One
A 141 Two
A 234 Two
B 981 One
C 121 One
C 111 One

Expected Output

Id Nums Vals
B 981 One
C 121 One
C 111 One

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE TestMultipleVals ( Id VARCHAR(1) , Nums INT , Vals VARCHAR(100) ) GO INSERT INTO TestMultipleVals SELECT 'A',141,'One' UNION ALL SELECT 'A',141,'Two' UNION ALL SELECT 'A',234,'Two' UNION ALL SELECT 'B',981,'One' UNION ALL SELECT 'C',121,'One' UNION ALL SELECT 'C',111,'One' GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- SELECT Id,Nums,Vals FROM TestMultipleVals t WHERE t.Vals = 'One' AND NOT EXISTS ( SELECT NULL FROM TestMultipleVals t2 WHERE t2.id = t.id and t2.Vals <> t.Vals ); -- ```

Output-1

 ```-- Id Nums Vals ---- ----------- --------------- B 981 One C 121 One C 111 One (3 rows affected) -- ```

Solution – 2

 ```-- SELECT Id,Nums,Vals FROM ( SELECT * , SUM( CASE WHEN t.Vals = 'One' THEN 1 ELSE 0 END ) OVER(PARTITION BY Id) a , SUM( CASE WHEN t.Vals <> 'One' THEN 1 ELSE 0 END ) OVER(PARTITION BY Id) b FROM TestMultipleVals t )k WHERE a >= b -- ```

Output-2

 ```-- Id Nums Vals ---- ----------- --------------- B 981 One C 121 One C 111 One (3 rows affected) -- ```

Solution – 3

 ```-- SELECT t.* FROM ( SELECT Id FROM TestMultipleVals t GROUP BY Id HAVING SUM(CASE WHEN t.Vals <> 'One' THEN 1 ELSE 0 END)=0 AND SUM(CASE WHEN t.Vals = 'One' THEN 1 ELSE 0 END) >= 1 )k INNER JOIN TestMultipleVals t ON t.Id = k.Id -- ```

Output-3

 ```-- Id Nums Vals ---- ----------- --------------- B 981 One C 121 One C 111 One (3 rows affected) -- ```

Solution – 4

 ```-- SELECT * FROM TestMultipleVals k WHERE EXISTS ( SELECT NULL FROM TestMultipleVals t WHERE t.Id = k.Id HAVING SUM(CASE WHEN t.Vals <> 'One' THEN 1 ELSE 0 END)=0 AND SUM(CASE WHEN t.Vals = 'One' THEN 1 ELSE 0 END) >= 1 ) -- ```

Output-4

 ```-- Id Nums Vals ---- ----------- --------------- B 981 One C 121 One C 111 One (3 rows affected) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com