Set value to last one

Transact-SQL
,
1

Hi to all
i have a data as below

Id	skuid	Task	Seq	TempId	MilestoneId
10031	11015	T1	1.00	1	NULL
10032	11015	t2	2.00	2	NULL
10033	11015	t3	3.00	3	111416
10034	11015	t4	4.00	4	NULL
10035	11015	t5	5.00	5	NULL
10036	11015	t6	6.00	6	NULL
10037	11015	t7	7.00	7	111358
10038	11015	t8	8.00	8	NULL
10039	11015	t9	9.00	9	NULL
10040	11015	t10	10.00	10	111414

here you can see the Milestoneid = Null , i want to set the milestoneid as latest milestoneid like below

Id	skuid	Task	Seq	TempId	MilestoneId	ReMileStoneId
10031	11015	T1	1.00	1	NULL	        111416
10032	11015	t2	2.00	2	NULL	        111416
10033	11015	t3	3.00	3	111416	        111416
10034	11015	t4	4.00	4	NULL	        111358
10035	11015	t5	5.00	5	NULL	        111358
10036	11015	t6	6.00	6	NULL	        111358
10037	11015	t7	7.00	7	111358	        111358
10038	11015	t8	8.00	8	NULL	        111414
10039	11015	t9	9.00	9	NULL	        111414
10040	11015	t10	10.00	10	111414	        111414

base on the (SkuId) and (Seq) i have to define a milestone id
suppose at id 10033 has a milestoneid = 111416 so at seruence 1.00 and 2.00 must have to containt 111416 as a milestone id (ReMileStoneId in secont tabulation format)

Please help me out

2 
SELECT  *
FROM
	tbl a
    OUTER APPLY 
    (
		SELECT TOP ( 1 )
			MileStonId AS ReMileStonId
		FROM
			tbl b
		WHERE
			b.Seq >= a.Seq
			AND a.skuId = b.skuId
			AND b.MileStoneId IS NOT NULL
		ORDER BY
			b.Seq
     ) b
1 Like
3

Hello JamesK

thanks for your answer

i have done by below way

SELECT
	CriticalPathId,
	skuid,
	Task,
	OrderSeq,
	TempTaskId,
	MilestoneId,
	(SELECT TOP 1
		FIRST_VALUE(MilestoneId) OVER (ORDER BY OrderSeq ASC)
	FROM CriticalPath FV
	WHERE FV.SkuId = 11015
	AND FV.MilestoneId IS NOT NULL
	AND FV.CriticalPathId >= CP.CriticalPathId)  AS ReMileStoneId
FROM CriticalPath CP
WHERE SkuId = 11015

Please suggest me which one is optimal solution

4

Since you are on SQL 2012 or 2014, FIRST_VALUE is perhaps better. You can run both queries side by side and look at the query plans to see which performs better. I suspect the windowing function would be more efficient, but I am guessing.