# Non-overlapping ranges in sequential data

Consider the following structure:

```CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`v` int(11) DEFAULT NULL
) ENGINE=InnoDB;
```

And the following data:

```INSERT INTO `t1` VALUES (1,1),(1,2),(2,3),(2,4),(2,5),(1,6),(1,7);
```
```+----+---+
| id | v |
+----+---+
|  1 | 1 |
|  1 | 2 |
|  2 | 3 |
|  2 | 4 |
|  2 | 5 |
|  1 | 6 |
|  1 | 7 |
+----+---+
```

hash-mysql regulars inquired if it would be possible to extract sequential non-overlapping ranges, in relation for each `id`. Here is a sample output, based on the test data:

```+----+----------+----------+
| id | min(a.v) | max(a.v) |
+----+----------+----------+
|  1 |        1 |        2 |
|  2 |        3 |        5 |
|  1 |        6 |        7 |
+----+----------+----------+
```

A proposed solution (thanks Jon!) would be to create two derived tables, each extracting the previous and next element in the chain:

```-- Pair the min with the closest max.
SELECT v1.v      AS r1
, MIN(v2.v) AS r2
, v1.id     AS id
, (MIN(v2.v) - v1.v) AS xrange
FROM (
SELECT t1.*
FROM t1
LEFT JOIN t1 AS t2
ON t1.v-1 = t2.v
AND t1.id  = t2.id
WHERE t2.id IS NULL
) v1
JOIN (
SELECT t1.*
FROM t1
LEFT JOIN t1 AS t2
ON t1.v+1 = t2.v
AND t1.id  = t2.id
WHERE t2.id IS NULL
) v2
ON v1.v <= v2.v
AND v1.id = v2.id
GROUP BY id
, r1
ORDER BY r1
, xrange DESC
```

The output from this solution would look like:

```+----+----+----+--------+
| R1 | R2 | ID | XRANGE |
+----+----+----+--------+
|  1 |  2 |  1 |      1 |
|  3 |  5 |  2 |      2 |
|  6 |  7 |  1 |      1 |
+----+----+----+--------+
```

Running each derived table separately shows the individual logic blocks:

This solution would also cover overlapping ranges.

Window functions, such as lead and lag can be used to handle this a little more cleanly. Unfortunately, MySQL does not implement them, so the derived table approach is required.