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

  • Here's your original data as a Temp Table to persist the test data to make "playing" easier...

      DROP TABLE IF EXISTS #Temp;
    GO
    CREATE TABLE #Temp
    (
    ID INT NOT NULL PRIMARY KEY
    ,VAL INT NULL
    )
    ;
    INSERT INTO #Temp WITH (TABLOCK)
    (ID, VAL)
    VALUES (1,1000)
    ,(2,null)
    ,(3,null)
    ,(4,null)
    ,(5,2000)
    ,(6,null)
    ,(7,null)
    ,(8,null)
    ;

    Here's one solution for use prior to SQL Server 2017 without using a "Quirky Update" to do the "Data Smear".  SQL Server 2017 and up has a nice "First_Value" function that would have made this a bit easier.

     SELECT  ID
    ,Val
    ,New_Val = v.LastVal
    FROM #Temp t
    CROSS APPLY
    (
    SELECT TOP 1
    ca.VAL
    FROM #Temp ca
    WHERE ca.Val IS NOT NULL
    AND ca.ID <= t.ID
    ORDER BY ID DESC
    )v(LastVal)
    ORDER BY t.ID
    ;

    Here are the results...

    Also, please use the code window for code and results for future posts.  It'll make the code easier to read and allows for space-indents.

    I also believe that LAG might be able to make this work but I ran out of time for this post.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thank you very much

  • Thanks for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I believe that this code is faster.  It's based on code by Itzik Ben-Gan.

    SELECT ID
    , t.VAL
    , CAST(SUBSTRING(MAX(CAST(ID AS BINARY(5)) + CAST(t.VAL AS BINARY(5))) OVER(ORDER BY t.ID ROWS UNBOUNDED PRECEDING), 6, 5) AS INT) AS New_Val
    FROM #Temp AS t

    Here is the comparison.  (Table name edited to remove underscores.)

    /*  Jeff's version.  */
    Table '#Temp_____00000001087D'. Scan count 9, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


    /* My version. */
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#Temp_____00000001087D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I agree with Drew... the code he posted does a single scan and not a scan with a wad of seeks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I just did a deep dive on that code.  ANSI NULLs are the key there!  Really cool code!  Thanks, again, Drew.  Do you have a link where Itzik provides this great and really simple idea?

    Note to self: Today, I learned something new AND useful!  Awesome data-smear method.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    I just did a deep dive on that code.  ANSI NULLs are the key there!  Really cool code!  Thanks, again, Drew.  Do you have a link where Itzik provides this great and really simple idea?

    Note to self: Today, I learned something new AND useful!  Awesome data-smear method.

    The Last non NULL does use this as one of its methods, but I don't think that's where I originally saw it.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Aye.  Thanks for the link.  The solution that you posted is even simpler than either of those two and now I understand about why you say it's not where you think you saw it.

    Either way, once I took a look at the breakdown in the code, it incredibly elegant and simple at the same time.  Thanks, again, Drew.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Drew showed me his wonderful code to do this same thing on another thread.  I did a deep dive on it and the concept is wonderfully simple and it solved the problem with using MAX() OVER that I didn't solve in one of my other attempts.

    To demonstrate how insanely quick Drew's code is,  I "Modenized" the sample data.  Here's the code.  For those unfamiliar with "fnTally", you can get that at the similarly named link in my signature line at the bottom of this post or use one of your own to replace it (unless it has a loop or rCTE or other form of RBAR in it).

    --PREFORMANCE TEST. Absolutely mind blowing how fast it is.
    --===== Drop the Temp Tables to make reruns easier.
    DROP TABLE IF EXISTS #Temp,#Result;
    GO
    CREATE TABLE #Temp
    (
    ID INT NOT NULL PRIMARY KEY
    ,VAL INT NULL
    )
    ;
    --===== Create the Test Data in #Temp
    WITH
    cteGenRareVal AS
    (
    SELECT ID = ABS(CRYPT_GEN_RANDOM(4)%1000000)+1 --Random ID's 1 to 1 Million
    ,Val = ABS(CRYPT_GEN_RANDOM(4)%1000000000)+1 --Random Val's 1 to 1 Billion
    FROM dbo.fnTally(1,1000)
    )
    ,cteGenID AS
    (
    SELECT ID = t.N
    FROM dbo.fnTally(1,1000000)t
    )
    INSERT INTO #Temp WITH (TABLOCK)
    (ID, VAL)
    SELECT gid.ID
    ,rv.Val
    FROM cteGenID gid
    LEFT JOIN cteGenRareVal rv
    ON gid.ID = rv.ID
    ORDER BY gid.ID
    ;
    --===== Solve the problem and dump the results to a Temp Table
    -- to take display time out of the picture.
    SET STATISTICS TIME ON
    ;
    SELECT ID
    ,t.VAL
    ,NewVal = CAST(SUBSTRING(
    MAX(CAST(ID AS BINARY(5)) + CAST(t.VAL AS BINARY(5)))
    OVER(ORDER BY t.ID ROWS UNBOUNDED PRECEDING)
    ,6,5) AS INT)
    INTO #Result
    FROM #Temp AS t
    ORDER BY ID
    ;
    SET STATISTICS TIME OFF
    ;
    GO

    Here are the STATISTICS from my box... insanely fast code for what it does.  Haven't tried the "Quirky Update" method for this but I'm thinking it'll be real close to a dead-heat.

    (1000000 rows affected)
    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:
    CPU time = 890 ms, elapsed time = 880 ms.
    Warning: Null value is eliminated by an aggregate or other SET operation.

    (1000000 rows affected)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 1 through 10 (of 10 total)

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