Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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*
- SELECT
- Sequence, Subset,
- CASE WHEN Sequence = 1 OR Subset <> LAG(Subset, 1) OVER (ORDER BY Sequence)
- THEN 'New subset'
- ELSE 'Continuation'
- END
- FROM
- @Data
Add Comment
Please, Sign In to add comment