Get data from previous row if a particular column is blank

I want to get the previous row value in a particular column if it is blank. How can I do this may be using lag function.

create table [test].[dbo].[test](
CusName nvarchar(50) NULL,

-- Insert some data
insert [test].[dbo].[test] (CusName, Value)
values ('CustomerA', 151),
('', 1400),
('', 2600),
('CustomerB', 111),
('', 359)


Appreciate it if you can give some suggestion on how to achieve this.
Thank you


please see the below link ... hope this helps !!! :slight_smile:

the links way is ... using FIRST VALUE .. ROWS unbounded PRECEEDING
all SQL Server 2012 .. or greater stuff !!!


i was able to do it .. but in a different slightly long way !!!
if it helps great ! :slight_smile:

please click arrow tot he left for drop create data
drop table #data

create table #data(
CusName nvarchar(50) NULL,

-- Insert some data
insert #data (CusName, Value)
values ('CustomerA', 151),(null, 1400),(null, 2600),(null,334),
       ('CustomerB', 111),(null, 359),(null,4444),(null,222),(null,56),
	   ('CustomerC', 123),(null, 345),(null,555),(null,4),(null,678),(null,666)

select 'sample data ', * from #data

; with 
    rn_cte as 
 select ROW_NUMBER() over(order by (select null)) as rn , * from #data 
   cte_notnull_rn as 
select ROW_NUMBER() over(order by rn) as rn12 ,a.* from 
	    select rn, CusName from rn_Cte where CusName is not null 
           union all 
        select max(rn) +1, 'Last Row' from rn_cte 
	    ) a 
   cte_fin as 
     select a.rn12 as arn12,a.CusName as acusname,a.rn as minrn ,  b.rn12 as brn12,b.CusName as bcusname,b.rn as maxrn 
	      cte_notnull_rn a 
		 cte_notnull_rn b 
		   a.rn12 + 1 = b.rn12
	      'SQL OUTPUT',
	   cte_fin a 
	   rn_cte b 
		  b.rn BETWEEN a.minrn and a.maxrn-1    


So what happens if the following is done before the query:

CREATE CLUSTERED INDEX ixc_temp_data_value ON #data([Value]);

Would you care to explain the result?

Do you think non-deterministic queries are a good idea?

Do you think it might be better to explain basic relational theory to the OP?

Hi ifor

Sure I can do that

Please give me some time


how do you know what order the data is in?

As mike01 noted, we must know the order of data. I've added an identity column to the table to provide an order. I also added more rows so that the same cust name appearing multiple times could be tested.

create table [dbo].[test](
id int identity(1, 1) NOT NULL,
CusName nvarchar(50) NULL,

-- Insert some data
TRUNCATE TABLE [dbo].[test];
insert [dbo].[test] (CusName, Value)
values ('CustomerA', 151), ('', 1400), ('', 2600),
('CustomerB', 111), ('', 359),
('CustomerC', 333), ('', 334), ('', 335),
('CustomerA', 2151), ('', 21400), ('', 22600)
SELECT * FROM dbo.test ORDER BY id;

;WITH cte_CusNames AS (
    SELECT id, CusName
    FROM dbo.test
    WHERE CusName <> ''
SET CusName = lookup_CusName.CusName
FROM dbo.test t
    SELECT TOP (1) CusName
    FROM cte_CusNames cc
    WHERE >
) AS lookup_CusName
WHERE (t.CusName IS NULL OR t.CusName = '')
SELECT * FROM dbo.test ORDER BY id;

Hi Mike and Scott

With regards to order by mentioned by both of you

I used
Order by (select null)

Which means
The order in which data is inserted
Into the table

Unless an order by needs to be specified

Please let me know if I am missing


What makes you think a relation/table has an order?

Hi. ..IFOR

Could you please explain

That to me

Thank you

Hi ifor

I mean it may need to be explained in a different way

Order by ( select null )
If this is what you are talking about

What is your opinion ..

Are you saying
That there is NO such thing as
Order in which data inserted into table

Could you please explain
I am more than happy to learn .

Not sure what you mean .


Yes, that is exactly what SQL Server itself states. Unless you explicitly specify an order, no order is guaranteed. If it's a new table, SQL is almost certainly going to load it in input order, so an identity is virtually sure, but it's not 100%. And I would certainly not rely on (SELECT NULL) in an order by to produce consistent results.

In relational theory, a relation (table) is an unordered set.
In SQL Server, due to the way it is implemented, a table can often appear to have a default order but it is not guaranteed. ie Only the result set, or windowed function, can be ordered. ORDER BY (SELECT NULL) should only be used when ORDER BY is required and you really do not care about the order. This is unlikely in production code.

With your test data, putting a clustered index on [Value], changed the apparent order of the table and completely messed up your query. The result of a query should be deterministic. ie The same results should be returned regardless of indexing etc. (Better indexing should only affect the speed at which the result is returned.)

With this sort of question you can:

  1. suggest that they have an ordering column; like Scott.
  2. suggest the original data was loaded from a list, where order has meaning, like an Excel sheet and the best thing to do is to add another column, like line number, before importing the data into SQL server.

Thanks Scott

Thanks ifor

I kind of had that in the back of my mind

Somewhere I read that . Long long time ago

Will keep that in mind..


Hi ScottPletcher,
It worked, thank you so much