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