MS SQL 2012: find number sequences

Question

I have a table like this:

    WITH S AS (
    SELECT 'B' type, 1 number
UNION SELECT 'B', 2
UNION SELECT 'B', 3
UNION SELECT 'B', 4
UNION SELECT 'B', 5
UNION SELECT 'A', 6
UNION SELECT 'A', 7
UNION SELECT 'B', 8
UNION SELECT 'B', 9
UNION SELECT 'B', 10
UNION SELECT 'C', 11
UNION SELECT 'A', 12
UNION SELECT 'B', 13
UNION SELECT 'B', 14
UNION SELECT 'B', 15
UNION SELECT 'B', 16
UNION SELECT 'B', 17
UNION SELECT 'A', 18
UNION SELECT 'C', 19
UNION SELECT 'B', 20
UNION SELECT 'B', 21
    )

How can I get distinctive sequences of numbers which come in a row (say, 3 or more) for each type? E.g. for B 1~5 there will be something like 'B1', for B 8~10 - 'B2' etc. I suspect there should be something like combination of LEAD/LAG and DENSE_RANK(), but cannot figure out how to apply. Numbers are unique, if that matters.

The result should look like:

Type   Number  Sequence
-----------------------
B        1        B1
B        2        B1
B        3        B1
B        4        B1
B        5        B1
A        6        NULL
......................
B        8        B2
B        9        B2
B        10       B2
C        11       NULL
A        12       NULL
B        13       B3
....................
B        17       B3

I don't understand what you are trying to do here. Can you explain the requirements? — Sean Lange, Mar 27 '17 at 21:38

score 1 · Answer 1 · answered Mar 27 '17 at 22:19

One way would be to use a combination of DENSE_RANK() and a difference of row numbers.

WITH S AS (
    SELECT 'B' type, 1 number
UNION SELECT 'B', 2
UNION SELECT 'B', 3
UNION SELECT 'B', 4
UNION SELECT 'B', 5
UNION SELECT 'A', 6
UNION SELECT 'A', 7
UNION SELECT 'B', 8
UNION SELECT 'B', 9
UNION SELECT 'B', 10
UNION SELECT 'C', 11
UNION SELECT 'A', 12
UNION SELECT 'B', 13
UNION SELECT 'B', 14
UNION SELECT 'B', 15
UNION SELECT 'B', 16
UNION SELECT 'B', 17
UNION SELECT 'A', 18
UNION SELECT 'C', 19
UNION SELECT 'B', 20
UNION SELECT 'B', 21
    ) 
SELECT type, 
       number, 
       sequence = CASE 
            WHEN type = 'B' 
            THEN 'B' + CAST(DENSE_RANK() OVER (ORDER BY CASE WHEN type = 'B' THEN 0 ELSE 1 END, RN) AS VARCHAR(10)) 
            END
FROM (
    SELECT *, RN = ROW_NUMBER() OVER (ORDER BY number) - ROW_NUMBER() OVER (PARTITION BY type ORDER BY number)
    FROM S
    ) AS T
ORDER BY number;

score 0 · Answer 2 · answered Mar 27 '17 at 22:35

There was a reply, just for a few minutes and it contained the exactly solution I want (the rest I can figure out):

SELECT type, R1 = min(number), R2 = max(number) FROM (
SELECT *, number - ROW_NUMBER() OVER (PARTITION BY type ORDER BY number) Grp From S
) A
GROUP BY type

Could not rate the answer - it was already deleted by then. The solution is about finding "Gaps and Islands" - now I know.

MS SQL 2012: find number sequences

2 Answers2