naera naera - 3 months ago 30
SQL Question

row_number / dense_rank (Group & Island)

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);

FROM @data;


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.