By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE X
(
ID INT IDENTITY(1,1) PRIMARY KEY,
v INT
);
INSERT INTO X
SELECT 121 UNION ALL SELECT NULL UNION ALL SELECT NULL
UNION ALL SELECT 312 UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
UNION ALL SELECT 123 UNION ALL SELECT NULL UNION ALL SELECT NULL
UNION ALL SELECT 415 UNION ALL SELECT 416 UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
UNION ALL SELECT 200;
16 rows affected
select *
from x
order by id
| ID | v |
|---|---|
| 1 | 121 |
| 2 | null |
| 3 | null |
| 4 | 312 |
| 5 | null |
| 6 | null |
| 7 | null |
| 8 | 123 |
| 9 | null |
| 10 | null |
| 11 | 415 |
| 12 | 416 |
| 13 | null |
| 14 | null |
| 15 | null |
| 16 | 200 |
select x.*,
(case when v is null then dense_rank() over (partition by v order by null_grp)
end) as newcolumn
from (select x.*,
count(v) over (order by id) as null_grp
from x
) x;
| ID | v | null_grp | newcolumn |
|---|---|---|---|
| 2 | null | 1 | 1 |
| 3 | null | 1 | 1 |
| 5 | null | 2 | 2 |
| 6 | null | 2 | 2 |
| 7 | null | 2 | 2 |
| 9 | null | 3 | 3 |
| 10 | null | 3 | 3 |
| 13 | null | 5 | 4 |
| 14 | null | 5 | 4 |
| 15 | null | 5 | 4 |
| 1 | 121 | 1 | null |
| 8 | 123 | 3 | null |
| 16 | 200 | 6 | null |
| 4 | 312 | 2 | null |
| 11 | 415 | 4 | null |
| 12 | 416 | 5 | null |
Warning: Null value is eliminated by an aggregate or other SET operation.