Technical Article

Finding Data Islands

,

Finding data islands is the reverse of finding gaps within a sequence of numbers. Instead of finding those numbers that are missing from a sequence, we want to find the sequnetial groups of numbers around the gaps. These are called data islands.

First, lets create a table with a list of seqential numbers with some numbers missing as well as a few duplicate numbers.

DECLARE @t TABLE (col1 INT)

INSERT INTO @t VALUES

(1),(2),(3),(5),(6),(8),(8),(9),(10),(11),(12),(12),(14),(15),(18),(19);

The DENSE_RANK function is used to create a sequential ranking upon which we can later group to find the islands of consecutive numbers. Note, that you can't use the RANK function, since it will create gaps in ranking if there are duplicates (i.e. ties) in the data set. DENSE_RANK will always create a sequential ranking even if there are ties.

WITH cte AS

(

    SELECT

        col1,

        DR = col1 - DENSE_RANK() OVER(ORDER BY col1)

    FROM @t

)

SELECT

    Low = MIN(col1),

    High = MAX(col1)

FROM cte

GROUP BY DR

I hope you find this helpful.

Peter Wehner

DECLARE @t TABLE (col1 INT)
INSERT INTO @t VALUES
(1),(2),(3),(5),(6),(8),(8),(9),(10),(11),(12),(12),(14),(15),(18),(19);

WITH cte AS
(
SELECT
col1,
DR = col1 - DENSE_RANK() OVER(ORDER BY col1)
FROM @t
)
SELECT
Low = MIN(col1),
High = MAX(col1)
FROM cte
GROUP BY DR

Rate

4.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

4.75 (4)

You rated this post out of 5. Change rating