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

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 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/