Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

Advertisements