Tags

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


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

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

Expected output

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

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

1 https://msbiskills.com/2018/02/22/sql-puzzle-the-zero-blank-puzzle-sql-interview-question/
2 https://msbiskills.com/2017/02/15/sql-puzzle-select-columns-which-doesnt-have-all-zero-value-ii/
3 https://msbiskills.com/2017/02/14/sql-puzzle-select-columns-which-doesnt-have-all-zero-value/
4 https://msbiskills.com/2016/07/21/sql-puzzle-ignore-zeros-till-you-get-the-first-non-zero-value/
5 https://msbiskills.com/2016/03/10/sql-puzzle-the-make-zero-puzzle/
6 https://pawankkmr.wordpress.com/2015/05/02/t-sql-query-the-non-zero-puzzle/
7 https://pawankkmr.wordpress.com/2015/03/22/439/

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.

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

Advertisements