I need to calculate sequence numbers for consecutive values. That sounds like a job for
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.