CODEWITHSUNDEEP
mysqlsql
Angela Kay
Angela Kay

Reputation: 45

How do I find gaps in ranges of numbers?

I have a table which has an id, as well as start/end values with represent a range of numbers. I am looking to find the first gap of a certain size (or larger) between existing ranges. For Example:

|   id  | start | end |
|-------|-------|-----|
| xz132 | 2     | 5   |
| fd754 | 9     | 12  |
| sb825 | 16    | 23  |

(EDIT: IDs are not incremental)

if I am looking for the first gap of size 1, it should return the range 1-1 as available.

if I'm looking for a gap of size 3, it should return range 6-8 as available.

if I'm looking for a gap of size 6, it should return either null to indicate no gaps, or 24-29 to indicate adding another record at the end of the table.

Upvotes: 2

Views: 1487

Answers (4)

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

Took into account the fact that IDs may not be consecutive; I needed a trick to allow search before the first record as well, that's why I "insert" on the fly a dummy pseudo-record of zeros. That is, of course, provided that ID >= 1; otherwise you should use another value, or another trick :-). "3" should be a parameter.

live demo

select a.end+1 as RangeFrom, b.start-1 as RangeTo
from 
(select * from test union all select 0, 0, 0) a left join 
(select * from test order by start) b on 
     b.start = (select min(start) from test x where x.start > a.end)
where b.start - a.end - 1 >= 3
order by 1
limit 1;

Upvotes: 0

trincot
trincot

Reputation: 350262

Here is a solution using variables:

select @end+1 start, start-1 end
from   (select * from ranges order by start) as base,
       (select @gap := 3, @end := 0) as init
where  if(@gap < start - @end, 1, if(@end := end, 0, 0))
limit  1;

Replace the number assigned to @gap with the desired gap size.

For @gap := 1, it returns (1, 1)
For @gap := 3, it returns (6, 8)
For @gap := 4, it returns no data

Upvotes: 3

Omid S.
Omid S.

Reputation: 761

you can use this simple query :

select * from `test`.`test_1` as out1 where `to` 
< ( select min( `from`  ) from `test`.`test_1` as in1 where in1.`from`   
> out1.`to`  )+ XX;

you can play with XXX to get the gap you want and this gives you all of them, you can use "order by from" and then " limit 1 " to get the first one

Upvotes: 0

Luke Bajada
Luke Bajada

Reputation: 1842

SELECT * FROM t WHERE (t.end+1)-t.start >= gap LIMIT 1;

Explanation

t is the name of your table

To get the gap, you need to subtract the start from the end and add 1.

gap is the gap of your choosing

LIMIT 1 will get you the first result.

Demo here

Upvotes: 0

Related Questions