Create Partition Based On The Difference Between Subsequent Row Indices In Sql Server 2012

I am using SQL Server 2012. I want to create a row_number based on whether the index in subsequent rows are increasing by 1 or more. For example, say I have a table that looks like

Solution 1:

Try this:

    ;with cte as
    (select*, 
     coalesce(row_index - (lag(row_index) over (orderby event)),1) diff
     from tbl
    ),

    cte2 as
    (select*, 
     (selectmax(diff) 
      from cte c 
      where c.row_index <= d.row_index
      ) minri
     from cte d
     )

    select event, row_index, minri, 
    dense_rank() over (orderby minri) rn 
    from cte2

• The first CTE gets the differences using the lag function (available from SQL Server 2012 onwards).
• The next CTE calculates when the difference exceeds 1 and assigns all records after that point to a 'group', until the next difference <> 1 is found. This is the key step in grouping.
• The last step is to use dense_rank over the indicator calculated in the previous step to get the row numbers as required.

This solution does have a limitation in that it will fail if the differences are not in increasing order i.e. if you have two more values in the sample data such as 52 and 53, it will classify them into group 3 instead of creating a new group.

Demo

Update: The below approach can overcome the above limitation:

    ;with cte as
    (select *, 
     coalesce(row_index - (lag(row_index) over (orderbyevent)),1) diff
     from tbl)
    ,cte2 as
    (select *,
     diff - coalesce(diff - (lag(diff) over (orderbyevent)),0) tmp
     from cte d)

     selectevent,row_index, 
     1 + sum(casewhen tmp >= diff then0else1end) over (orderbyevent) risum
     from cte2

Again the first step remains the same. But in step 2, we check only for transition to a different value of the difference between successive values, instead of using a min/max function. The ranking then uses a conditional sum to assign a group for each value in the original data.

Demo

This can be further simplified to:

selectevent, row_index, 
sum(casewhen diff <= 1then0else1end) over (orderbyevent) as rb
from
(select *, 
 row_index - (lag(row_index) over (orderbyevent)) diff
 from tbl
) s

Solution 2:

With a huge number of records it may performs badly, but give it a try:

;with T as (
 selectevent, rowindex, rowindex-row_number() over (orderbyevent) as rn from YourTableName
)
selectevent, (select count(distinct rn)+1from T where rn<TT.rn) as seq_id
 from T TT orderbyevent