Selecting contiguous block of records in mysql

algorithm, mysql, sql

Solution

There is a simple trick to collapse consecutive entries into a single group. If you group by (row_number - entry), the entries that are consecutive will end up in the same group. Here is an example demonstrating what I mean:

Query:

SELECT phonenum, @curRow := @curRow + 1 AS row_number, phonenum - @curRow
from phonenums p
join (SELECT @curRow := 0) r

Results:

|    PHONENUM | ROW_NUMBER | PHONENUM - @CURROW |
-------------------------------------------------
| 27100070000 |          1 |        27100069999 |
| 27100070001 |          2 |        27100069999 |
| 27100070002 |          3 |        27100069999 |
| 27100070003 |          4 |        27100069999 |
| 27100070004 |          5 |        27100069999 |
| 27100070005 |          6 |        27100069999 |
| 27100070008 |          7 |        27100070001 |
| 27100070009 |          8 |        27100070001 |
| 27100070012 |          9 |        27100070003 |
| 27100070015 |         10 |        27100070005 |
| 27100070016 |         11 |        27100070005 |
| 27100070040 |         12 |        27100070028 |

Notice how the entries that are consecutive all have the same value for `PHONENUM - @CURROW`. If we group on that column, and select the min & max of each group, you have the summary (with one exception: you could replace the END value with `NULL` if START = END if that's a requirement):

Query:

select min(phonenum), max(phonenum) from
(
  SELECT phonenum, @curRow := @curRow + 1 AS row_number
  from phonenums p
  join (SELECT @curRow := 0) r
) p
group by phonenum - row_number

Results:

| MIN(PHONENUM) | MAX(PHONENUM) |
---------------------------------
|   27100070000 |   27100070005 |
|   27100070008 |   27100070009 |
|   27100070012 |   27100070012 |
|   27100070015 |   27100070016 |
|   27100070040 |   27100070040 |

Demo: http://www.sqlfiddle.com/#!2/59b04/5

Problem

I have a table in MySql 5 of phone numbers. The simple structure is ``` Accounts id varchar(32) NOT NULL ``` The records are as follows ``` 27100070000 27100070001 27100070002 27100070003 27100070004 27100070005 27100070008 27100070009 27100070012 27100070015 27100070016 27100070043 ``` I need to sort through this data and group contiguous blocks of numbers into number ranges. I'm open to implementing the solution in C# LINQ but server-side MySql is first prize. Is there a way in MySql to get this data summarised so that the output is as below? ``` Start | End ------------------------- 27100070000 | 27100070005 27100070008 | 27100070009 27100070012 | 27100070015 27100070016 | NULL 27100070043 | NULL ```

Original source