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.