sqlsql-server-2016

SQL Server Add row number each group


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..


Solution

  • 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.