# Gaps And Islands

Not too long ago, I helped a co-worker solve a problem.  The quick version of the problem is, we want to collate together common occurrences of an event.  In this case, it’s log records by IP address.  If the same IP address shows up 3 times in a row, we want to know that there were 3 events, followed by the next IP, and so on.  If that first IP shows up later on, it’s a new chain.

This is another way of formulating the island problem.  Itzik Ben-Gan covers it wonderfully in his chapter on gaps and islands in SQL Server MVP Deep Dives, which chapter Manning has made available for free.

In my case, here’s a sample and the solution:

```CREATE TABLE #Temp
(
LogID INT,
ErrorDate DATETIME,
);

INSERT into dbo.#Temp
(
LogID,
ErrorDate,
)
VALUES
(173371, '2014-04-07 09:47:35', 'IP 1'),
(173372, '2014-04-07 09:47:36', 'IP 1'),
(173373, '2014-04-07 09:47:37', 'IP 1'),
(173374, '2014-04-07 10:01:35', 'IP 2'),
(173375, '2014-04-07 10:02:35', 'IP 3'),
(173376, '2014-04-07 10:03:04', 'IP 3'),
(173377, '2014-04-07 10:03:05', 'IP 3'),
(173378, '2014-04-07 10:03:18', 'IP 3'),
(173379, '2014-04-07 10:18:26', 'IP 1'),
(173380, '2014-04-07 10:18:26', 'IP 1'),
(173381, '2014-04-07 10:18:26', 'IP 1'),
(173382, '2014-04-07 10:18:27', 'IP 3');

WITH records AS
(
SELECT
LogID,
ErrorDate,
ROW_NUMBER() OVER (ORDER BY LogID) - ROW_NUMBER() OVER (ORDER BY IPAddress, LogID) AS grp
FROM
#Temp
)
SELECT
MIN(LogID) AS MinimumID,
MAX(LogID) AS MaximumID,
COUNT(*) AS NumberOfRows,