Unite adjacent ranges from different rows in MySQL

Question

Suppose there is a company with several departments. Each of them has one or a few ranges of internal phone numbers assigned to it. Assignment is stored in one database table, but in different manner for different departments, like this:

dept_name    range_start    range_end

Sales        10             19
Sales        20             29
HR           30             49
Finance      60             65
Finance      84             93

How can adjacent ranges be selected as united, so that for Sales dept. there will be just one row in results, like this:

dept_name    range_start    range_end

Sales        10             29
HR           30             49
Finance      60             65
Finance      84             93

?

Why are they different ranges in the data to begin with if they are continuous? — Mike Brant, Dec 03 '14 at 16:02
It's just rows in a database. why not fix it to combine those rows? — Mike Brant, Dec 03 '14 at 16:11
Are you wanting to check for continuous rows or just combine those rows where `dept_name = 'Sales'`? — sgeddes, Dec 03 '14 at 16:27
@MikeBrant Well, I don't know how to fix them either. This post is just example, in fact the table has around 30k rows. — Mikhail Batcer, Dec 03 '14 at 17:42
@sgeddes I want to combine all rows with continuous data, which belong to same departments. In other words, continuous data for same dept. should not be broken apart into several rows. — Mikhail Batcer, Dec 03 '14 at 18:17
SO perhaps write a script to investigate the rows and consolidate them. I would think that doing that on a one time basis is much more efficient than trying to run some really crazy innefficient query all the time. — Mike Brant, Dec 03 '14 at 22:11

score 1 · Accepted Answer · edited May 23 '17 at 12:27

It can be done by finding all the range starts, all the range ends, and then joining them together in the right order.

Here's a SQLfiddle that demonstrates this concept: http://sqlfiddle.com/#!2/6cd35/24

And the query...

SELECT oStart.dept_name, oStart.range_start, oEnd.range_end
FROM
(
    SELECT strt.dept_name, strt.range_start, @rownum := @rownum + 1 AS rank
    FROM
      (
        SELECT d1.dept_name, d1.range_start, d1.range_end
        FROM DepartmentPhoneRanges AS d1
        LEFT JOIN DepartmentPhoneRanges AS d2
          ON d1.dept_name = d2.dept_name AND d1.range_start = d2.range_end + 1
        WHERE d2.dept_name IS NULL
      ) AS strt
      , 
      (SELECT @rownum := 0) r
    ORDER BY dept_name, range_start
) AS oStart
INNER JOIN (
    SELECT end.dept_name, end.range_end, @rownum2 := @rownum2 + 1 AS rank2
    FROM 
      (
        SELECT d1.dept_name, d1.range_start, d1.range_end
        FROM DepartmentPhoneRanges AS d1
        LEFT JOIN DepartmentPhoneRanges AS d2
          ON d1.dept_name = d2.dept_name AND d1.range_end = d2.range_start - 1
        WHERE d2.dept_name IS NULL
      ) AS end
      , 
      (SELECT @rownum2 := 0) r
    ORDER BY dept_name, range_end
) AS oEnd
ON oStart.dept_name = oEnd.dept_name AND oStart.rank = oEnd.rank2
;

NB - with no ROW_NUMBER() function in mySQL (As there is in SQL Server) I used the trick in OMG Ponies' answer from this post as a stand-in

Unite adjacent ranges from different rows in MySQL

1 Answers1