SQL Server Add row number each group

Question

I working on a query for SQL Server 2016. I have order by serial_no and group by pay_type and I would like to add row number same example below

row_no | pay_type | serial_no
   1   |    A     | 4000118445
   2   |    A     | 4000118458
   3   |    A     | 4000118461
   4   |    A     | 4000118473
   5   |    A     | 4000118486
   1   |    B     | 4000118499
   2   |    B     | 4000118506
   3   |    B     | 4000118519
   4   |    B     | 4000118521
   1   |    A     | 4000118534
   2   |    A     | 4000118547
   3   |    A     | 4000118550
   1   |    B     | 4000118562
   2   |    B     | 4000118565
   3   |    B     | 4000118570
   4   |    B     | 4000118572

Help me please..

Answer 1

You can assign groups to adjacent pay types that are the same and then use row_number(). For this purpose, the difference of row numbers is a good way to determine the groups:

select row_number() over (partition by pay_type, seqnum - seqnum_2 order by serial_no) as row_no,
       t.*
from (select t.*,
             row_number() over (order by serial_no) as seqnum,
             row_number() over (partition by pay_type order by serial_no) as seqnum_2
      from t
     ) t;

This type of problem is one example of a gaps-and-islands problem. Why does the difference of row numbers work? I find that the simplest way to understand is to look at the results of the subquery.

Here is a db<>fiddle.