Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ Fill Missing Value Puzzle ] – In this puzzle we have to fill the missing values in the sample input table . Please check out the sample input and expected output for details.
Sample Input
| Value | ayValue |
| A | 1 |
| 23 | |
| 21 | |
| 22 | |
| B | 34 |
| 31 | |
| 89 | |
| C | 222 |
| 10 |
Expected Output
| Value | ayValue |
| A | 1 |
| A | 23 |
| A | 21 |
| A | 22 |
| B | 34 |
| B | 31 |
| B | 89 |
| C | 222 |
| C | 10 |
Rules/Restrictions
- The solution should be should use “SELECT” statement or “CTE”.
- Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script
Use the below script to generate the source table and fill them up with the sample data.
--Create Table
CREATE TABLE [dbo].[MissingValue]
(
[Value] [varchar](1) NULL,
[ayValue] [int] NULL
)
GO
--Insert Data
INSERT INTO [dbo].[MissingValue](Value,ayValue)
VALUES
('A', 1),
('', 23),
('', 21),
('', 22),
('B', 34),
('', 31),
('', 89),
('C', 222),
('', 10)
--Verify Data
SELECT Value,ayValue FROM [MissingValue]
|
Update May 14 | Solution
--
/************ SOLUTION 1 ****************/
;WITH CTE2 AS
(
SELECT p.Value,p.ayValue
,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rrnk FROM [dbo].[MissingValue] p
)
SELECT
c.Value,c.ayValue,c.rrnk,
(
SELECT d.Value from CTE2 d WHERE d.rrnk = (Select max(e.rrnk) from CTE2 e
Where e.rrnk <= c.rrnk and (e.value != '' ))
) NewValue
FROM CTE2 c
--
|
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
;with cte
as
(
SELECT value,
ayValue
,row_number() over ( order by (select null)) as rwnum1
FROM #MissingValue
)
,CTE1
AS
(
select a.ayValue,MAX(B.rwnum1)AS RNK from cte a
cross join cte b
where a.rwnum1>=b.rwnum1 and b.Value !=”
GROUP BY a.ayValue
)
select a.Value,b.ayValue from cte a
join CTE1 b
on a.rwnum1=b.RNK
LikeLiked by 1 person
Great Sanjay ….
LikeLike
select max(Col1Val) over(partition by (select null) order by %%physloc%% rows unbounded preceding) as [Col1Val],
Col2Val
from tblMissingColVals
LikeLike
Excellent solution Michael !!
LikeLike
https://polldaddy.com/js/rating/rating.jsCheers! And thanks for creating all of these. I absolutely love them. Brilliant work you have done here. I am late to the party as I just discovered these a couple of weeks ago. I started from the beginning and am going to work my way up. Have completed 30. 4 stumped me though.
LikeLike
That is to say, a total of 4 puzzles stumped me in the first 30 puzzles.
LikeLike
;with A as
(
SELECT Value,ayValue,ROW_NUMBER()over(order by (select 1)) as r FROM #MissingValue
)
select
case when r<( select r from A where value='B') then 'A'
when r<( select r from A where value='C') then 'B'
else 'C'
end as value ,ayValue
from A
LikeLiked by 1 person
Excellent 👍
LikeLike
;WITH CTE1
AS
(
SELECT Value,ayValue,IIF(Value”,1,0) A
FROM #Test
),
CTE2
AS
(
SELECT VALUE,ayValue,SUM(A) OVER (ORDER BY ayValue) Grp
FROM CTE1
)
SELECT ayValue,max(Value)OVER (ORDER BY Grp)
FROM CTE2
LikeLiked by 1 person
SELECT ayValue,max(Value) OVER (ORDER BY %%Physloc%%)
FROM [dbo].[MissingValue]
LikeLiked by 1 person
WITH CTE AS (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ROWID
FROM [dbo].[MissingValue]
)
SELECT
(select top 1 Value from CTE where ROWID 0 order by ROWID desc) as Value,
ayValue
FROM CTE t
LikeLiked by 1 person
;
with cte as
(select distinct value from [MissingValue]
where value != ”)
,cte2 as (select *, 1 as rank from cte)
,cte3 as (select [MissingValue] .value, [MissingValue].ayvalue
, case when cte2.rank is not null then cte2.rank else 0 end as grouper, ROW_NUMBER() OVER (ORDER BY (SELECT 100)) as row_number
from [MissingValue] left join cte2 on [MissingValue].value = cte2.value)
, cte4 as (select *, sum(grouper) over(order by row_number) gr from cte3)
, cte5 as (select distinct value, gr from cte4 where value != ”)
select cte5.value, cte4.ayvalue from cte4 left join cte5 on cte4.gr = cte5.gr
LikeLike
WITH CTE2 AS
(
SELECT p.Value,p.ayValue
,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM [dbo].[MissingValue] p
),cte3 as( Select a.value ,
b.ayValue,
b.value as vb,
b.rnk brn,
a.rnk as arn,
max(a.rnk) over(partition by b.ayValue) as maxrnk
from cte2 b
left join cte2 a
on b.rnk >= a.rnk
and a.value ”)
select
value,
ayValue
from cte3
where arn = maxrnk
order by brn
LikeLike
WITH CTE2 AS
(
SELECT p.Value,p.ayValue
,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM [dbo].[MissingValue] p
),CTE3 as (Select value,ayvalue,rnk,case when value ” then 1 else 0 end as mark from CTE2
),CTE4 AS (Select value,ayvalue,rnk,sum(mark) over(order by rnk) as running_total from CTE3)
Select a.value,b.ayvalue from CTE4 a INNER join CTE4 b on a.running_total = b.running_total and a.value ”
LikeLike