August 3, 2022 at 4:30 pm
Hello.
I need help with the following query:
declare @T table (
ID int not null primary key
, VAL int null
);
insert into @T
(ID, VAL)
values
(1, 1000), (2, null), (3, null), (4, null)
, (5, 2000), (6, null), (7, null), (8, null)
;
select T.*
, (
select S.VAL
from @T S
where S.ID = (
select min(SS.ID)
from @T SS
where SS.ID > T.ID and T.VAL is not null
)
) [NEW_VAL]
from @T T
--result:
--ID VAL NEW_VAL
--1 1000 NULL
--2 NULL NULL
--3 NULL NULL
--4 NULL NULL
--5 2000 NULL
--6 NULL NULL
--7 NULL NULL
--8 NULL NULL
--Needed result:
--ID VAL NEW_VAL
--1 1000 1000
--2 NULL 1000
--3 NULL 1000
--4 NULL 1000
--5 2000 2000
--6 NULL 2000
--7 NULL 2000
--8 NULL 2000
Thank You
August 3, 2022 at 9:26 pm
select T.*, T2.VAL
from @T T
outer apply (
select top (1) t2.*
from @T t2
where t2.ID <= T.ID and
t2.VAL is not null
order by t2.ID desc
) as T2
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 4, 2022 at 3:19 pm
Please don't cross post. It fragments the replies. Other thread https://www.sqlservercentral.com/forums/topic/query-help-299#post-4072505
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply