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.
I was able to address this using LAG():

SELECT
    Sequence, Subset,
    CASE WHEN Sequence = 1 OR Subset &lt;&gt; LAG(Subset, 1) OVER (ORDER BY Sequence)
        THEN 'New subset'
        ELSE 'Continuation'
        END
FROM
    @Data


This returns "New subset" for records #1, #4, and #6.  Apparently LAG() partitions slightly differently from ROW_NUMBER().

Obviously this doesn't provide row numbers, but it helped me reach the goal of identifying consecutive sequences of numbers, when the subset identifier can repeat.