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

sanjay singh

said:;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

Pawan Kumar Khowal

said:Great Sanjay ….

LikeLike

Michael Brown

said:select max(Col1Val) over(partition by (select null) order by %%physloc%% rows unbounded preceding) as [Col1Val],

Col2Val

from tblMissingColVals

LikeLike

Pawan Kumar Khowal

said:Excellent solution Michael !!

LikeLike

Michael Brown

said: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

Michael Brown

said:That is to say, a total of 4 puzzles stumped me in the first 30 puzzles.

LikeLike

MD Fahim (@The_MDFahim)

said:;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

Pawan Kumar Khowal

said:Excellent 👍

LikeLike

jerryjames08outlookcom

said:;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

jerryjames08outlookcom

said:SELECT ayValue,max(Value) OVER (ORDER BY %%Physloc%%)

FROM [dbo].[MissingValue]

LikeLiked by 1 person

Gregor Kralj

said: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

Rishabh Mishra

said:;

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

Ashutosh Sharma

said: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

Ashutosh Sharma

said: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