Tags

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


SQL PUZZLE | Refill missing data with nearest data using Single SELECT | SQL Interview Question

In this puzzle you have to replace the NULL values present in the column Vals with the next NON null value. If the next value is also NULL then replace that with the previous NON null value. All this you have to do in a single select. Please check out sample input and expected output.

Sample Input

Id Vals
1 100
2 100
3 NULL
4 200
5 120
6 12
7 NULL
8 121
9 NULL
10 105
11 103
12 111
13 NULL

Expected output

Id Vals NewVals
1 100 100
2 100 100
3 NULL 200
4 200 200
5 120 120
6 12 12
7 NULL 121
8 121 121
9 NULL 105
10 105 105
11 103 103
12 111 111
13 NULL 111

Script – DDL and INSERT sample data

--

CREATE TABLE GetNearestData
(
	 Id INT IDENTITY (1,1)
	,Vals INT
)
GO

INSERT INTO GetNearestData VALUES
(100),
(100),
(NULL),
(200),
(120),
(12),
(NULL),
(121),
(NULL),
(105),
(103),
(111),
(NULL)
GO

SELECT * FROM GetNearestData
GO

--

SOLUTION – 1

--

SELECT Id, Vals
	,COALESCE(Vals,LEAD(Vals) OVER (ORDER BY Id),LAG(Vals) OVER (ORDER BY Id)) NewVals
FROM GetNearestData
GO

--

OUTPUT – 1

--

Id          Vals        NewVals
----------- ----------- -----------
1           100         100
2           100         100
3           NULL        200
4           200         200
5           120         120
6           12          12
7           NULL        121
8           121         121
9           NULL        105
10          105         105
11          103         103
12          111         111
13          NULL        111

(13 rows affected)

--

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