SQL Query to find holes between min_numbers and max_number in a table

Question

Quick question for SQL gurus.

I have a table that contains, among other things, two columns - min_number and max_number. I tried unsuccessfully to write a query that finds the first hole n of size between the minimum and maximum numbers

Example

     min    max
1.   100    200
2.   250    300
3.   330    400

If I want to find a hole of size 50, the 1st maximum max 200 will be returned (there is a hole 50 between it and the line 2 min), hole 20 will return the 2nd maximum max 300, etc. If there was no hole of a suitable size, the last max (400) will be returned.

thanks

Answer 1

Edited: The final answer is below.

Why do so many SQL questions forget the table name?

-- Buggy: should reference (lo.max + 1)
SELECT lo.max + 1 AS min_range
    FROM example lo, example hi
    WHERE hi.min - (lo.max - 1) >= 40   -- Example won't work with 50
      AND NOT EXISTS (SELECT * FROM example AS mid
                         WHERE mid.min > lo.max
                           AND mid.max < hi.min
                     )

NOT EXISTS - , .

" ".

, " " UNION:

...
UNION
SELECT MAX(max)+1
    FROM example
    WHERE NOT EXISTS(
        SELECT lo.max + 1 AS min_range
            FROM example lo, example hi
            WHERE hi.min - (lo.max - 1) >= 40   -- Example won't work with 50
              AND NOT EXISTS (SELECT * FROM example AS mid
                                 WHERE mid.min > lo.max
                                   AND mid.max < hi.min
                             )
            )

SELECT , .

---

SQL . ( ), . (, , "--" ):

SELECT MIN(lo.max + 1) AS min_range
    FROM example lo, example hi
    WHERE hi.min - (lo.max + 1) >= 40   -- Example won't work with 50
      AND NOT EXISTS (SELECT * FROM example AS mid
                         WHERE mid.min > lo.max
                           AND mid.max < hi.min
                     )

UNION - ... .

:

SELECT MIN(lo.max + 1) AS min_range
    FROM example lo, example hi
    WHERE hi.min - (lo.max + 1) >= 40   -- Example won't work with 50
      AND NOT EXISTS (SELECT * FROM example AS mid
                         WHERE mid.min > lo.max
                           AND mid.max < hi.min
                     )
UNION
SELECT MAX(solo.max)+1
    FROM example AS solo
    WHERE NOT EXISTS(
        SELECT MIN(lo.max + 1) AS min_range
            FROM example lo, example hi
            WHERE hi.min - (lo.max - 1) >= 40   -- Example won't work with 50
              AND NOT EXISTS (SELECT * FROM example AS mid
                                 WHERE mid.min > lo.max
                                   AND mid.max < hi.min
                             )
            )

MAX, (, example.max solo.max. , .

---

UNION OR, , , , , , :

SELECT MIN(lo.max + 1) AS min_range
    FROM example lo, example hi
    WHERE (hi.min - (lo.max + 1) >= 40
           AND NOT EXISTS (SELECT * FROM example AS mid
                              WHERE mid.min > lo.max
                                AND mid.max < hi.min
                          )
          )
       OR lo.max = (SELECT MAX(solo.max) FROM Example AS Solo)
;

, OR lo.max, hi.max; .

---

- UNION , SQL MIN. , , , MIN NULL, . , UNION NULL, ; "", MIN SELECT NOT EXISTS, (NULL ) , . , OR - SQL NULL.

, , UNION FROM. :

SELECT MIN(min_range)
    FROM (SELECT (lo.max + 1) AS min_range
              FROM example lo, example hi
              WHERE hi.min - (lo.max + 1) >= 49
                AND NOT EXISTS (SELECT * FROM example AS mid
                                   WHERE mid.min > lo.max
                                     AND mid.max < hi.min
                               )
          UNION
          SELECT MAX(solo.max + 1) AS min_range
              FROM example AS solo
         );

UNION , ; ( , ). .

, , :

INSERT INTO Example(min, max)
    SELECT MIN(min_range) AS min, MIN(min_range) + (50 - 1) AS max
        FROM (SELECT (lo.max + 1) AS min_range
                  FROM example lo, example hi
                  WHERE hi.min - (lo.max + 1) >= 50
                    AND NOT EXISTS (SELECT * FROM example mid
                                       WHERE mid.min > lo.max
                                         AND mid.max < hi.min
                                   )
              UNION
              SELECT MAX(solo.max + 1) AS min_range
                  FROM example AS solo
             );

Answer 2

SELECT
     MIN(T1.max_value)
FROM
     My_Table T1
LEFT OUTER JOIN My_Table T2 ON
     T2.min_value BETWEEN (T1.max_value + 1) AND (T1.max_value + @range)
WHERE
     T2.id IS NULL

, , , , max_value min_value.

NOT EXISTS. , .

, , ? , , ? , , , , .

Answer 3

select min(n+1) from myTable where n+1 NOT IN  (select n from myTable)

• R Doherty

Answer 4

SQL - AIUI, , O (n ^ 2) . , , .

, , , , ​​ , , , .

, , .

Answer 5

MySQL ? , .

Answer 6

" 20 2s max 300 .." - 2 (300) 3 (330) 30 ( , , 29, ). , " " ? " ", , , 1, > 20 2?

, - , , - ( , MyTable RowID, MinVal MaxVal ):

SELECT TOP 1
        a.RowID,
        a.MinVal,
        a.MaxVal, -- this is the value you want to return
        ISNULL(b.MinVal, 9999) AS MinVal_NextRow,
        ISNULL(b.MinVal, 9999) - a.MaxVal AS Diff
FROM    MyTable a
        LEFT JOIN MyTable b ON a.RowID = ( b.RowID - 1 )
WHERE   ( ISNULL(b.MinVal, 9999) - a.MaxVal ) = 20

In this example, the first line is selected, where the gap is exactly 20. If you searched for the first space of at least 20, you can change the WHERE clause to:

WHERE   ( ISNULL(b.MinVal, 9999) - a.MaxVal ) >= 20

The query replaces an arbitrary large number (9999) in, when the line is the last available - this is what the last (largest) MaxVal returns if there are no spaces of the appropriate size. You will need to configure this number to make sense for your data (i.e. more than any possible values ​​in the data).