Below you will find test data to play with. What I need is a dense_rank, rank, row_number whatever function to get the rows numbered as in "NoRequired".
I tested every rank and row_number function and even tried to use WITH statements, but didn't come up with a solution yet.
I guess the main problem here is, that the "rank function" needs to skip a sequence once there is a different key value in between...
DECLARE @data TABLE (ID int IDENTITY(1,1), X int, NoRequired int);
INSERT INTO @data(X, NoRequired)
VALUES (1000,1), (1000,1), (800,2), (600,3), (1000,4), (1000,4), (800,5);
This is a "group and islands" problem. One simple method is to use lag and a cumulative sum:
select d.*, sum(case when prev_x = x then 0 else 1 end) over (order by id) as NoRequired from (select d.*, lag(x) over (order by id) as prev_x from @data d ) d;
The idea is to identify rows where the value changes and then do a cumulative sum of those values.