CODEWITHSUNDEEP
mysqlgaps-and-islands
Daryl B
Daryl B

Reputation: 525

Next group of sequential numbers mysql

I have a table with a lot of phone numbers in it. And a lot of them are in sequence. What I want is to be able to ask for a block of 'n' sequential numbers and then get the issued with them.

For example my table is like this:

'number'
'2220381'
'2220382'
'2220383'
'2220384'
'2220386'
'2220387'
'2220389'
'2220390'
'2220391'
'2220392'
'2220393'
'2220394'
'2220395'

I have found plenty of threads on how to do this with oracle and MSSQL and how to find Gaps but the functions they are using to find Islands don't seem to be available in MySQL But what I am interested in is a block of sequence with size n.

So what I want to do is ask this database for 5 sequential numbers (I dont care what they are but want the next 5) for example. And have it return in this case 2220386, 2220387, 2220388, 2220389, 2220390

My table is basically a pool, and only has the one column. I have tried all sorts of joins onto itself but have not had much luck.

Upvotes: 1

Views: 134

Answers (1)

Drew
Drew

Reputation: 24959

create table nums
(
    num int not null
);

-- truncate table nums;
insert nums (num) values (1),(2),(14),(15),(16),(17),(20),(21),(22),(23),(24),(30),(81),(120),(121),(122),(123),(124);

select min(t2.num)
from
( 
select t1.num
from nums t1 
where 5 in (select count(*) from nums where num in (t1.num,t1.num+1,t1.num+2,t1.num+3,t1.num+4))
) t2;

Answer: 20

Upvotes: 1

Related Questions