Replace null value with value from the previous row

Question

I can not to solve in this problem because column : ID is not unique.

I don't know to replace null value with value from the previous row.

AS IS

ID	NAME
001	NULL
001	A
001	NULL
001	NULL
001	B
001	NULL

TO BE

ID	NAME
001	NULL
001	A
001	A
001	A
001	B
001	B

And assuming you're on the latest version of SQL Server, @DaleK , so you can use `IGNORE NULL`. Based on the sample data `MAX` would work (as the value is ascending), however, if not this is a "classic" gaps and islands issue. — Thom A, May 25 '23 at 09:50
you don't have any sortable column in your example, so this will be hard. usually you need a date or id so you know what order the rows appear, it's not like excel — siggemannen, May 25 '23 at 09:54

SelVazi · Answer 1 · 2023-05-25T11:52:05.867

1

As @siggemannen mentioned in the comments, you need a date/timestamp or a unique id per ID to make it possible.

Assuming you have a unique id per ID called rn_id, the window function MAX() with a range clause can help you only if NAME are ordered alphabetically :

select id, max(NAME) over ( partition by ID order by rn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as NAME 
from mytable

Result :

id	NAME
1	null
1	A
1	A
1	A
1	B
1	B

Demo here

If not ordered alphabetically then we will need to use two window functions :

The IIF() function will return 1 when a value is present, otherwise it will return zero. The windowed sum will show the running total increasing, which will create a grouping for a value and it's following nulls.

Then first_value() will catch the last non null value per group :

with cte as (
  SELECT 
      ID,
      rn_id,
      NAME,
      SUM(IIF(NAME IS NULL, 0,1)) OVER (partition by ID ORDER BY rn_id ASC) AS grp
    FROM 
      mytable
)
select id, First_value(NAME) over ( partition by ID, grp order by rn_id) as NAME_
from cte

Demo here

edited May 25 '23 at 11:52

answered May 25 '23 at 09:53

SelVazi

10,028
2
13
29

3

I didn't DV but `order by (select null)` is going to be non-deterministic, you can see for example here https://dbfiddle.uk/ECaeXolV where this solution doesn't work because there is an index sorting the rows in a different order. Unless you can identify an order then this is not going to work, plus `Name` might not be monotonically increasing anyway. – Charlieface May 25 '23 at 10:18
Actually, this answer might be wrong anyway :/. MAX(value) might take value from previous "null" group, consider for instance data that looks like this: Z NULL NULL A NULL NULL B. Everyone will get a Z – siggemannen May 25 '23 at 11:06
Good catch @siggemannen, I have revised my response to reflect this. – SelVazi May 25 '23 at 11:53

Replace null value with value from the previous row

1 Answers1