How to get row number for each null value?

I need to get row number for each record of null by sequence. Restart number when get a value in the row.

I have tried so far

select *
     , ROW_NUMBER() over (order by id) rn
from @tbl 

select *
     , ROW_NUMBER() over (partition by value order by id) rn
from @tbl 

declare @tbl table(id int, value int)
insert into @tbl values 
(1, null), (2, null), (3, null), (4, 1),(5, null), (6, null), (7, 1), (8, null), (9, null), (10, null)

select *
     , ROW_NUMBER() over (partition by value order by id) rn
from @tbl 

I’m getting this:

id, value,  rn
1   NULL    1
2   NULL    2
3   NULL    3
4   1       4
5   NULL    5
6   NULL    6
7   1       7
8   NULL    8
9   NULL    9
10  NULL    10

I want a result like this

id, value,  rn
1   NULL    1
2   NULL    2
3   NULL    3
4   1       1
5   NULL    1
6   NULL    2
7   1       1
8   NULL    1
9   NULL    2
10  NULL    3

How can I get desired result with sql query?

Answer

This approach uses COUNT as an analytic function over the value column to generate “groups” for each block of NULL values. To see how this works, just run SELECT * FROM cte using the code below. Then, using this computed group, we use ROW_NUMBER to generate the sequences for the NULL values. We order ascending by the value, which would mean that each NULL row number sequence would always begin with 1, which is the behavior we want. For records with a non NULL value, we just pull that value across into the rn column.

WITH cte AS (
    SELECT *, COUNT(value) OVER (ORDER BY id) vals
    FROM @tbl
)

SELECT id, value,
    CASE WHEN value IS NULL
         THEN ROW_NUMBER() OVER (PARTITION BY vals ORDER BY value)
         ELSE value END AS rn
FROM cte
ORDER BY id;

Demo