SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE foo
    (
    Sequence    TINYINT NOT NULL PRIMARY KEY,
    Subset  CHAR(1) NOT NULL
    );
INSERT INTO foo (Sequence, Subset) VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'A'),
    (4, 'B'),  -- New subset
    (5, 'B'),
    (6, 'A')   -- New subset
;
6 rows affected

 SELECT CASE WHEN subset != lag(subset) OVER (ORDER BY sequence) THEN 1 END AS rst, *
 FROM foo;


rst
Sequence
Subset


null
1
A

null
2
A

null
3
A

1
4
B

null
5
B

1
6
A


SELECT count(rst) OVER (ORDER BY sequence) AS grp, *
FROM (
  SELECT CASE WHEN subset != lag(subset) OVER (ORDER BY sequence) THEN 1 END AS rst, *
  FROM foo
) AS t1;


grp
rst
Sequence
Subset


0
null
1
A

0
null
2
A

0
null
3
A

1
1
4
B

1
null
5
B

2
1
6
A


Warning: Null value is eliminated by an aggregate or other SET operation.


SELECT row_number() OVER (PARTITION BY grp ORDER BY sequence) AS number,
  sequence,
  subset
FROM (
  SELECT count(rst) OVER (ORDER BY sequence) AS grp, *
  FROM (
    SELECT CASE WHEN subset != lag(subset) OVER (ORDER BY sequence) THEN 1 END AS rst, *
    FROM foo
  ) AS t1
) AS t2;


number
sequence
subset


1
1
A

2
2
A

3
3
A

1
4
B

2
5
B

1
6
A


Warning: Null value is eliminated by an aggregate or other SET operation.

rst	Sequence	Subset
null	1	A
null	2	A
null	3	A
1	4	B
null	5	B
1	6	A