Query Help

  • 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

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply