Can I calculate ROW_NUMBER() for only consecutive records?

I need to calculate sequence numbers for consecutive values. That sounds like a job for ROW_NUMBER()!

DECLARE @Data TABLE
    (
    Sequence    TINYINT NOT NULL PRIMARY KEY,
    Subset  CHAR(1) NOT NULL
    )
INSERT INTO @Data (Sequence, Subset) VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'A'),
    (4, 'B'),  -- New subset
    (5, 'B'),
    (6, 'A')   -- New subset

SELECT
    Sequence, Subset,
    ROW_NUMBER() OVER (PARTITION BY Subset ORDER BY Sequence) AS SeqWithinGroup
FROM
    @Data

I expected the PARTITION clause to make the count reset at each change in Subset, but instead SQL Server collects all values for a given Subset value and numbers them. Here's what I expected, and what I got:

Sequence Subset Expected Actual
-------- ------ -------- -----
1        A      1        1
2        A      2        2
3        A      3        3
4        B      1        1
5        B      2        2
6        A      *1*      *4*

When SQL reaches line #6, it resumes numbering subset "A", whereas I see it as the first line of a new subset that just happens to also be named "A".

Is there a way to make ROW_NUMBER() partition strictly, rather than the default behavior?

There are a number of questions here and elsewhere about counting consecutive values with SQL. However, I have not yet seen one that addresses repeated values in the PARTITION BY field(s). Most deal only with increasing values, often dates.

What we do here is,

• Calculate resets (column rst in code)

• sum() to get groups (column grp in code)

• Get the row_number() from the grouping.

Code,

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;

You can see the DBFiddle with results here

• https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=13de970857770ce27462565b4063e1d0

StackExchange Database Administrators Q#212713, answer score: 4