How to do custom row numbering?

Question

I have some data as follows:

[Row Number]
NULL
NULL
NULL
1
2
1
1
2
3
4
NULL
NULL
1

Assuming the data is already ordered in the way I want it, is it possible to assign new row numbers as follows?

[Row Number] [New Row Number]
NULL          1
NULL          2
NULL          3
1             4
2             4
1             5
1             6
2             6
3             6
4             6
NULL          7
NULL          8
1             9

The logic that I want is the following:

The [New Row Number] column should increment only when the [Row Number] column is NULL or 1. For those with [Row Number] greater than 1, they can be thought of as duplicates of the last 1 before that number.

Is there a way to do this in SQL Server 2016?

Following the comments, I have also included the columns which I have ordered my data on below:

[PN] [CN] [Row Number] [New Row Number]
NULL 1    NULL         1
NULL 2    NULL         2
NULL 3    NULL         3
NULL 11   NULL         4
NULL 12   NULL         5
1    4    1            6
1    5    2            6
2    6    1            7
3    7    1            8
3    8    2            8
3    9    3            8
3    10   4            8
4    13   1            9

I have ordered my data on [PN] then [CN] then [Row Number].

Just to rephrase my problem in another way, I want a row numbering system that considers each NULL value in the PN column as a distinct value and so the row number should increment. However, it should not increment when the [PN] is the same. [CN] is a unique identifier for each row i.e. no two rows have the same [CN]. Moreover, if [PN] is NULL, then [Row Number] would be NULL.

I did not think the positioning of the NULLs would matter previously, so my apologies for that!

Answer 1

This can be solved by a simple window aggregated summary:

DECLARE @data TABLE (data  int, expected int, inheritsort int)

INSERT INTO @data
SELECT  col1 AS data
,   col2 AS expected
,   ROW_NUMBER() OVER(ORDER BY @@trancount) AS inheritSort
FROM
(
    VALUES  (NULL, 1)
    ,   (NULL, 2)
    ,   (NULL, 3)
    ,   (1, 4)
    ,   (2, 4)
    ,   (1, 5)
    ,   (1, 6)
    ,   (2, 6)
    ,   (3, 6)
    ,   (4, 6)
    ,   (NULL, 7)
    ,   (NULL, 8)
    ,   (1, 9)
) t (col1,col2)


SELECT  *
,   SUM(CASE WHEN ISNULL(data, 1) = 1 THEN 1 ELSE 0 END) OVER(ORDER BY inheritsort ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rowIdActual
FROM    @data

Outputs:

data	expected	inheritsort	rowIdActual
NULL	1	1	1
NULL	2	2	2
NULL	3	3	3
1	4	4	4
2	4	5	4
1	5	6	5
1	6	7	6
2	6	8	6
3	6	9	6
4	6	10	6
NULL	7	11	7
NULL	8	12	8
1	9	13	9