Tags

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


SQL PUZZLE | Remove ALL if Others are Present Puzzle | SQL Interview Question

In this puzzle you have to get rows with the following conditions-

1 : If any Id has 1 row with All in the dat column that row should come.
2 : If any Id has multiple rows and one of the row has All in the dat column then the row with the All column should not come.

For more details please refer sample input and expected output.

Sample Input

Id Val Dat
1 a Simple
1 a All
1 a Double
2 b Triple
2 b All
3 c All
3 c Simple
3 c Double
4 e All
5 f All

Expected output

Id Val Dat
1 a Simple
1 a Double
2 b Triple
3 c Simple
3 c Double
4 e All
5 f All

Script – DDL and INSERT sample data

--

CREATE TABLE MyData
(
	 Id INT
	,Val VARCHAR(10)
	,Dat VARCHAR(20)
)
GO

INSERT INTO MyData VALUES
(1,'a','Simple'),
(1,'a','All'),
(1,'a','Double'),
(2,'b','Triple'),
(2,'b','All'),
(3,'c','All'),
(3,'c','Simple'),
(3,'c','Double'),
(4,'e','All'),
(5,'f','All')
GO

SELECT * FROM MyData
GO

--

SOLUTION 1 – USING PARTITION COUNT, CASE and CASE

--

SELECT Id,Val,Dat  FROM 
(
	SELECT * , COUNT(1) OVER (PARTITION BY Id ORDER BY Id) a , 
	CASE WHEN Dat = 'All' THEN 1 ELSE 0 END b
	FROM MyData
)k WHERE 1 = CASE WHEN a = 1 AND b = 1 THEN 1
			      WHEN a > 1 AND b = 0 THEN 1 
			 ELSE 0 END
GO

--

OUTPUT 1

--

Id          Val        Dat
----------- ---------- --------------------
1           a          Simple
1           a          Double
2           b          Triple
3           c          Simple
3           c          Double
4           e          All
5           f          All

(7 rows affected)

--

SOLUTION 2 – USING PARTITION COUNT, CASE and OR

--

SELECT Id,Val,Dat FROM 
(
	SELECT * , COUNT(1) OVER (PARTITION BY Id ORDER BY Id) a , 
	CASE WHEN Dat = 'All' THEN 1 ELSE 0 END b
	FROM MyData
)k WHERE k.a = 1 OR ( k.b = 0 AND k.a > 1 )
GO


--

OUTPUT 2

--

Id          Val        Dat
----------- ---------- --------------------
1           a          Simple
1           a          Double
2           b          Triple
3           c          Simple
3           c          Double
4           e          All
5           f          All

(7 rows affected)

--

SOLUTION 3 – Using Outer Apply and ISNULL

--

SELECT k.Id,k.Val, ISNULL(n.Dat,'All') Dat FROM 
(
	SELECT DISTINCT Id,Val
	FROM MyData
)k
OUTER APPLY
(
	SELECT Dat 
	FROM MyData b
	WHERE Dat <> 'All' AND k.Id = b.Id AND b.Val = k.Val
)n

--

OUTPUT 3

--

Id          Val        Dat
----------- ---------- --------------------
1           a          Simple
1           a          Double
2           b          Triple
3           c          Simple
3           c          Double
4           e          All
5           f          All

(7 rows affected)

--

SOLUTION 4 – Using UNION ALL and COUNT

--

Select ID,Val,Dat FROM MyData Where Dat != 'All'
UNION ALL
SELECT ID,VAL,Max(dat) FROM MYData GROUP by ID,val having count(*) = 1 and MAX(dat) = 'All'


--

OUTPUT 4

--

Id          Val        Dat
----------- ---------- --------------------
1           a          Simple
1           a          Double
2           b          Triple
3           c          Simple
3           c          Double
4           e          All
5           f          All

(7 rows affected)

--

Enjoy 🙂

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com