getting previous value thats not null

Question

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

Answer 1

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.

Answer 2

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

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   

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   

Answer 3

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:

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:

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.