the number of rows in a group of items in a sequence

The key here is to create a sequence id you can use in a window function to get the count. You won’t be able to do it in one query because window functions can’t be combined, but you can pull it off with a subquery or CTE.

To determine the sequence number for a row, you need to count the number of times the group key has changed in the preceding rows. So to determine the changes, create an inner query that checks if the current group key is different from the previous by using the lag window function. Use a case statement that results in 1 or 0 depending on if the lagged value is different from the current. The outer query then just has to sum up the values for all rows preceding up to the current.

Once you have the sequence number, you can use a count window function to count all the rows with matching numbers.

WITH src AS ( -- cte to mimic table.
    SELECT * 
    FROM (VALUES 
        (1, N'00001.000001', N'00001.000001', 2),
        /* ... test data ... */
        (10, N'00003.000001', N'00003.000001', 1)
    ) [src] ( [Id],[MinCode],[MaxCode],[ExpectedResult] )
)
SELECT src.Id, MinCode, MaxCode, ExpectedResult
    , COUNT(1) OVER (PARTITION BY seq.SequenceId) [Result]
FROM src 
INNER JOIN (
    SELECT x.Id, SUM(x.IsNew) OVER (ORDER BY Id ROWS UNBOUNDED PRECEDING) [SequenceId]
    FROM (
        SELECT Id, CASE WHEN LAG(MinCode) OVER (ORDER BY Id) <> MinCode THEN 1 ELSE 0 END [IsNew]
        FROM src 
    ) x
) seq ON seq.Id = src.Id
ORDER BY Id

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top