SQL Server partitioning when null

Question

I have a sql server table like this:

Value   RowID   Diff
153     48      1
68      49      1
50      57      NULL
75      58      1
65      59      1
70      63      NULL
66      64      1
79      66      NULL
73      67      1
82      68      1
85      69      1
66      70      1
118     88      NULL
69      89      1
67      90      1
178     91      1

How can I make it like this (note the partition after each null in 3rd column):

Value   RowID   Diff
153     48      1
68      49      1
50      57      NULL
75      58      2
65      59      2
70      63      NULL
66      64      3
79      66      NULL
73      67      4
82      68      4
85      69      4
66      70      4
118     88      NULL
69      89      5
67      90      5
178     91      5

Answer 1

It looks like you are partitioning over sequential values of RowID. There is a trick to do this directly by grouping on RowID - Row_Number():

select
  value,
  rowID,
  Diff,
  RowID - row_number() over (order by RowID) Diff2
from
  Table1

Notice how this gets you similar groupings, except with distinct Diff values (in Diff2):

| VALUE | ROWID |   DIFF | DIFF2 |
|-------|-------|--------|-------|
|   153 |    48 |      1 |    47 |
|    68 |    49 |      1 |    47 |
|    50 |    57 | (null) |    54 |
|    75 |    58 |      1 |    54 |
|    65 |    59 |      1 |    54 |
|    70 |    63 | (null) |    57 |
|    66 |    64 |      1 |    57 |
|    79 |    66 | (null) |    58 |
|    73 |    67 |      1 |    58 |
|    82 |    68 |      1 |    58 |
|    85 |    69 |      1 |    58 |
|    66 |    70 |      1 |    58 |
|   118 |    88 | (null) |    75 |
|    69 |    89 |      1 |    75 |
|    67 |    90 |      1 |    75 |
|   178 |    91 |      1 |    75 |

Then to get ordered values for Diff, you can use Dense_Rank() to produce a numbering over each separate partition - except when a value is Null:

select
  value,
  rowID,
  case when Diff = 1
  then dense_rank() over (order by Diff2)
  else Diff end as Diff
from (
  select
    value,
    rowID,
    Diff,
    RowID - row_number() over (order by RowID) Diff2
  from
    Table1
) T

The result is the expected result, except keyed off of RowID directly rather than off of the existing Diff column.

| VALUE | ROWID |   DIFF |
|-------|-------|--------|
|   153 |    48 |      1 |
|    68 |    49 |      1 |
|    50 |    57 | (null) |
|    75 |    58 |      2 |
|    65 |    59 |      2 |
|    70 |    63 | (null) |
|    66 |    64 |      3 |
|    79 |    66 | (null) |
|    73 |    67 |      4 |
|    82 |    68 |      4 |
|    85 |    69 |      4 |
|    66 |    70 |      4 |
|   118 |    88 | (null) |
|    69 |    89 |      5 |
|    67 |    90 |      5 |
|   178 |    91 |      5 |