SQL PUZZLE | THE MAX Zero Puzzle | SQL Interview Question

In this puzzle for each dat we have to find the Maximum value. The only catch is that if any of the value is 0 from the dat the replace the Max with 0

The challenge is to do this in a single SELECT.

For more details please refer sample input and expected output.

Sample Input

Expected output

Script – DDL and INSERT sample data

--

CREATE TABLE TheZeroPUZZLE
(
	 ID INT
	,Dat VARCHAR(10)
	,Val INT
)
GO

INSERT INTO TheZeroPUZZLE VALUES
(1,'Pawan', 0  ),
(2,'Pawan', 1  ),
(3,'Pawan', 2  ),
(4,'Jonny', 1 ),
(5,'Jonny', 2 ),
(6,'Jonny', 3 ),
(7,'Avtaar', 0 ),
(8,'Avtaar', 1 )
GO

SELECT * FROM TheZeroPUZZLE
GO


--

SOLUTION 1

--

SELECT * , 
	CASE WHEN 
			  MIN(Val) OVER (PARTITION BY Dat) = 0 THEN 0 
		 ELSE
			  MAX(Val) OVER (PARTITION BY Dat) 
		 END Maxx
	FROM TheZeroPUZZLE
GO


--

OUTPUT 1

--

ID          Dat        Val         Maxx
----------- ---------- ----------- -----------
7           Avtaar     0           0
8           Avtaar     1           0
4           Jonny      1           3
5           Jonny      2           3
6           Jonny      3           3
1           Pawan      0           0
2           Pawan      1           0
3           Pawan      2           0

(8 rows affected)


--

Also checks Related Puzzles on Zero

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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.

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