Edited: final answer is at the bottom.
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
)
The NOT EXISTS clause is crucial - it ensures that you are only considering adjacent ranges.
This deals with the 'there is a gap big enough' case.
Nominally, you can deal with the 'there is no gap big enough' with a UNION clause:
...
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
)
)
The inner SELECT is a direct transcription of the first, indented.
The SQL above was untested. The first part works (especially on the test data) - but can produce multiple answers. So, it needs to be revised to (fixing, I think, an off-by-two error):
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
)
The UNION clause is giving me some grief...not producing the answer I expect.
Syntactically, I had to amend it to:
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
)
)
This circumvents problems with the keyword MAX being used as a column name (I could probably have written example.max instead of solo.max. But it isn't producing me the answer I expect.
A UNION is equivalent to an OR, certainly in this case, and this query seems to produce the answer I want:
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)
;
It is crucial that the OR clause cite lo.max and not hi.max; otherwise, you get the wrong answer.
OK - the UNION version is doomed, because SQL misdefines the behaviour of MIN. Specifically, if there are no rows that match, then MIN returns a single row with the value NULL, rather than returning no rows. That means that the first clause of the UNION returns a NULL when there are no rows found; the second clause can be 'fixed' by omitting the MIN from the SELECT inside the NOT EXISTS, but you still end up with two rows (a NULL and the correct value) from the statement, which is not really acceptable. So, the OR version is the one to use - and SQL bites again with NULL values.
Rigorously avoiding nulls can be done by framing the UNION in a table expression in the FROM clause. This ends up being slightly simpler:
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
);
The first half of the UNION can return any number of slots including zero; the second always returns a value (as long as there are any rows in the table at all). The outer query then chooses the lowest of these values.
This version can, of course, be used to allocate rows:
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
);