CODEWITHSUNDEEP
mysqlsql
Joe
Joe

Reputation: 326

Finding Gaps in number Ranges in DB

I had posted this question previously on how i can find if number(s) exist in between a number range. I was able to resolve it using the query below

  SELECT count(StartingNumber) FROM range
  WHERE 690  BETWEEN   StartingNumber and EndingNumber
    or 1800  BETWEEN   StartingNumber and EndingNumber
    or StartingNumber in ( SELECT StartingNumber
                            FROM range
                            WHERE StartingNumber BETWEEN 690 AND 1800);

Now i am just wondering if would be possible to find the gaps in the various number ranges:

So, if there is a range from 1 - 100 , 101 - 135, 150 - 200, 201 - 255, 255 - 270, 301 - 326

I want to find out the missing ranges 136 - 149, 217 - 300 etc. Also an pictorial representation of DB table columns is shown below :

enter image description here

Now I m trying to find out if would be possible to write an SQL query that will list the missing ranges 251 - 299, 1251 - 1299

Upvotes: 1

Views: 163

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Well, you could use subquery with ? cols that could specify your actual data ordering 

select * from (
    select *, (select idto 
               from table 
               where ? < t.? 
               order by ? desc LIMIT 1)+1 as MissingFrm,
           idfrm-1 as MissingTo
     from table t
)tt where idfrm <> MissingFrm;

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

Try this

select *
from
(
  select d1.hi + 1 tlo, min(d2.lo) - 1 thi
  from data d1
  join data d2 on d1.hi < d2.lo
  group by d1.hi
) t
where tlo < thi

dbfiddle demo

The subquery finds the ranges between hi and lo and the outer query just select the correct ranges.

Upvotes: 1

Related Questions