SQL Puzzle | THE AMAZING NULL PROBLEM | SQL Interview Question

In this puzzle we have to get distinct data from Id and Vals columns. Now if for any Id if you have data and NULL also then in that case we have to ignore the NULL row.

Please check the sample input and the expected output.

Sample Input

Id Val
1 10
1 11
1 NULL
1 NULL
2 10
5 10
3 NULL
4 NULL
4 NULL

Expected Output

Name Id X 1 X 9 X 10 A 2 A 4 A 6 U 4 U 9 U 12 B 5 B 6 B 7 B 8

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE NULLNOTNULL ( Id INT ,Val VARCHAR(10) ) GO INSERT INTO NULLNOTNULL VALUES (1,10), (1,11), (1,NULL), (1,NULL), (2,10), (5,10), (3,NULL), (4,NULL), (4,NULL) GO SELECT * FROM NULLNOTNULL GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

SOLUTION – 1 | Using COUNT … OVER (PARTITION BY ..) Clause

 ```-- SELECT Id,Val FROM ( SELECT Id,Val,COUNT(*) OVER (PARTITION BY Id) cnts FROM NULLNOTNULL GROUP BY Id,Val )a WHERE cnts = 1 OR (cnts > 1 AND Val IS NOT NULL) -- ```

Output-1

 ```-- Id Val ----------- ---------- 1 10 1 11 2 10 3 NULL 4 NULL 5 10 (6 rows affected) -- ```

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Related Puzzles

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/