CODEWITHSUNDEEP
sqlsql-serversql-server-2016rankingrow-number
Ethan Mark
Ethan Mark

Reputation: 309

How to do custom row numbering?

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!

Upvotes: -2

Views: 107

Answers (1)

siggemannen
siggemannen

Reputation: 9287

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

Upvotes: 2

Related Questions