getting previous value thats not null

ojmp2001 ojmp2001 121 Reputation points
2021-09-07T22:38:39.347+00:00

I have a table that has several columns I would like to get the previous value that is not null.
Create table #temp
(ID INT,
Testv Varchar(max)
)
Insert into #temp values (1,'')
Insert into #temp values (1,'abc')
Insert into #temp values (1,'def')
Insert into #temp values (1,'')
Insert into #temp values (1,'ijk')
Insert into #temp values (1,'')
Insert into #temp values (2,'xyz')
Insert into #temp values (2,'')
Insert into #temp values (2,'klm')
Insert into #temp values (2,'nop')

my desired output is as shown below
130052-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-09-08T01:30:23.383+00:00

    Hi @ojmp2001 ojmp2001 ,

    Welcome to Microsoft Q&A!

    After checking, below insert row is missing.

    Insert into #temp values (2,'hmn')  
    

    What is the version of your SQL Server?

    If your version is SQL Server 2012 and later, please refer below and check whether it is helpful to you.

    Create table #temp  
    (ID INT,  
    Testv Varchar(max)  
    )  
    Insert into #temp values (1,'')  
    Insert into #temp values (1,'abc')  
    Insert into #temp values (1,'def')  
    Insert into #temp values (1,'')  
    Insert into #temp values (1,'ijk')  
    Insert into #temp values (2,'hmn')  
    Insert into #temp values (2,'xyz')  
    Insert into #temp values (2,'')  
    Insert into #temp values (2,'klm')  
    Insert into #temp values (2,'nop')  
      
    ;with cte as (  
    select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by (select 1)) rn from #temp)  
    ,cte1 as (  
    select *,max(Testv) over (partition by id,c) Testvv from (  
    select *,c=count(Testv) over (partition by id order by rn)  
    from cte )a)  
    select ID,isnull(Testv,'')Testv,  
    isnull(lag(Testvv) over (partition by id order by rn),'') desired  
    from cte1   
    

    Output:

    ID	Testv	desired  
    1		  
    1	abc	  
    1	def	abc  
    1		def  
    1	ijk	def  
    2	hmn	  
    2	xyz	hmn  
    2		xyz  
    2	klm	xyz  
    2	nop	klm  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,096 Reputation points
    2021-09-08T15:45:39.547+00:00

    DO NOT USE THE SULOTION PRESENTED BY MelissaMa-msft !

    This solution is a non-deterministic query, which might return different results in different executions!

    Seems like he forgot that a table (especially without clustered index) is not an ordered SET of rows.
    https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

    130293-image.png

    For the sake of the discussion, since we are working with tiny tables, we can try to insert the rows in different order, and check the result which will be different.

    DROP TABLE IF EXISTS #temp  
    GO  
    Create table #temp (ID INT, Testv Varchar(max) )  
     Insert into #temp values (1,'abc')  
     Insert into #temp values (1,'')  
     Insert into #temp values (2,'nop')  
     Insert into #temp values (1,'')  
     Insert into #temp values (2,'klm')  
     Insert into #temp values (1,'def')  
     Insert into #temp values (2,'hmn')  
     Insert into #temp values (2,'xyz')  
     Insert into #temp values (1,'ijk')  
     Insert into #temp values (2,'')  
     GO  
      
     ;with cte as (  
     select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by (select 1)) rn from #temp)  
     ,cte1 as (  
     select *,max(Testv) over (partition by id,c) Testvv from (  
     select *,c=count(Testv) over (partition by id order by rn)  
     from cte )a)  
     select ID,isnull(Testv,'')Testv,  
     isnull(lag(Testvv) over (partition by id order by rn),'') desired  
     from cte1   
    

    130312-image.png

    DROP TABLE IF EXISTS #temp  
    GO  
    Create table #temp (ID INT, Testv Varchar(max) )  
     Insert into #temp values (1,'')  
     Insert into #temp values (1,'abc')  
     Insert into #temp values (1,'def')  
     Insert into #temp values (1,'')  
     Insert into #temp values (1,'ijk')  
     Insert into #temp values (2,'hmn')  
     Insert into #temp values (2,'xyz')  
     Insert into #temp values (2,'')  
     Insert into #temp values (2,'klm')  
     Insert into #temp values (2,'nop')  
     GO  
      
     ;with cte as (  
     select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by (select 1)) rn from #temp)  
     ,cte1 as (  
     select *,max(Testv) over (partition by id,c) Testvv from (  
     select *,c=count(Testv) over (partition by id order by rn)  
     from cte )a)  
     select ID,isnull(Testv,'')Testv,  
     isnull(lag(Testvv) over (partition by id order by rn),'') desired  
     from cte1   
    

    130277-image.png

    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2021-09-09T02:18:37.927+00:00

    Hi @ojmp2001 ojmp2001 ,

    Thanks for your update.

    I tried to add consecutive null values and found that my query was working.

    Please refer below:

    Create table #temp  
    (ID INT,  
    Testv Varchar(max)  
    )  
    Insert into #temp values (1,'')  
    Insert into #temp values (1,'abc')  
    Insert into #temp values (1,'def')  
    Insert into #temp values (1,'')  
    Insert into #temp values (1,'')  
    Insert into #temp values (1,'ijk')  
    Insert into #temp values (2,'hmn')  
    Insert into #temp values (2,'xyz')  
    Insert into #temp values (2,'')  
    Insert into #temp values (2,'')  
    Insert into #temp values (2,'klm')  
    Insert into #temp values (2,'nop')  
      
     ;with cte as (  
     select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by (select 1)) rn from #temp)  
     ,cte1 as (  
     select *,max(Testv) over (partition by id,c) Testvv from (  
     select *,c=count(Testv) over (partition by id order by rn)  
     from cte )a)  
     select ID,isnull(Testv,'')Testv,  
     isnull(lag(Testvv) over (partition by id order by rn),'') desired  
     from cte1   
    

    Output:
    130553-output1.png

    If you are doubt about the 'ROW_NUMBER() over (partition by id order by (select 1))' part, you could consider to add one identity column to fix the order.

    Please refer below:

    Create table #temp1  
    (  
    RowID int identity(1,1),  
    ID INT,  
    Testv Varchar(max)  
    )  
    Insert into #temp1 (ID,Testv)values (1,'')  
    Insert into #temp1 (ID,Testv)values (1,'abc')  
    Insert into #temp1 (ID,Testv)values (1,'def')  
    Insert into #temp1 (ID,Testv)values (1,'')  
    Insert into #temp1 (ID,Testv)values (1,'')  
    Insert into #temp1 (ID,Testv)values (1,'ijk')  
    Insert into #temp1 (ID,Testv)values (2,'hmn')  
    Insert into #temp1 (ID,Testv)values (2,'xyz')  
    Insert into #temp1 (ID,Testv)values (2,'')  
    Insert into #temp1 (ID,Testv)values (2,'')  
    Insert into #temp1 (ID,Testv)values (2,'klm')  
    Insert into #temp1 (ID,Testv)values (2,'nop')  
      
     ;with cte as (  
     select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by RowID) rn from #temp1)  
     ,cte1 as (  
     select *,max(Testv) over (partition by id,c) Testvv from (  
     select *,c=count(Testv) over (partition by id order by rn)  
     from cte )a)  
     select ID,isnull(Testv,'')Testv,  
     isnull(lag(Testvv) over (partition by id order by rn),'') desired  
     from cte1   
    

    Output:

    130581-output2.png
    If above output is not expected, please provide more sample data and expected output.

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.