Choosing a continuous block of records in mysql

Question

I have a table in MySql 5 phone numbers. Simple structure

Accounts id varchar(32) NOT NULL 

The entries are as follows

 27100070000 27100070001 27100070002 27100070003 27100070004 27100070005 27100070008 27100070009 27100070012 27100070015 27100070016 27100070043 

I need to sort this data and group continuous blocks of numbers into numerical ranges. I am open to implementing the solution in C # LINQ, but the MySql server is the first prize. Is there a way in MySql that this data is summed so that the result is lower?

 Start | End ------------------------- 27100070000 | 27100070005 27100070008 | 27100070009 27100070012 | 27100070015 27100070016 | NULL 27100070043 | NULL 

Answer 1

There is a simple trick to collapse consecutive records into one group. If you group (row_number - entry), records that are sequentially fall into one 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 | 

Note that records that are sequential have the same meaning for PHONENUM - @CURROW . If we group this column and select the min and max of each group, you have a summary (with one exception: you can replace the END value with NULL if START = END if this is 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