SQL - Find the lowest unused number

Question

I have a 'number' column in 2 different tables. This isn't the ID.

I have created a union like so:

SELECT number FROM table1 UNION SELECT number FROM table ORDER BY number ASC

This results in the following:

number
=====
1
2
3
5
6
8

How can I find the lowest unused number? In this case it would be 4. Once 4 has been used it would be 7, etc etc

There's a condition lurking around somewhere in this question looking for an opportunity to race right out and bite you! Be careful of race conditions. — O. Jones, Oct 02 '16 at 13:42

score 3 · Answer 1 · answered Oct 02 '16 at 13:05

Here is one way:

select min(number + 1)
from t
where not exists (select 1 from t t2 where t2.number = t.number + 1);

With two different tables, I would phrase this as:

select min(x)
from ((select min(number + 1) as x
       from t1 t
       where not exists (select 1 from t1 tt1 where tt1.number = t.number + 1)
             not exists (select 1 from t2 tt2 where tt2.number = t.number + 1)
      ) union all
      (select min(number + 1) as x
       from t2 t
       where not exists (select 1 from t1 tt1 where tt1.number = t.number + 1)
             not exists (select 1 from t2 tt2 where tt2.number = t.number + 1)
      )
     ) t;

This looks more complicated but it can use indexes on (number) in each table (if such indexes exist).

score 1 · Accepted Answer · answered Oct 02 '16 at 14:59

Assuming your number start from 1 always below query will give unused number

select min(rank) as Num from 
(select num,@curRank1 := @curRank1 + 1 AS rank from (SELECT num1 as num FROM t1 
UNION
SELECT num2 as num FROM t2) a1, (SELECT @curRank1 := 0) r ORDER BY num ASC) tab where num != rank;`

score 0 · Answer 3 · edited May 23 '17 at 12:01

Assuming that your variable number is an INTEGER.

First I would create a sequence of numbers in a range (the procedure described below was extracted from this solution: get the first N positive integers)

CREATE TABLE list_of_numbers (number INT NOT NULL PRIMARY KEY AUTO_INCREMENT)
CREATE PROCEDURE create_sequence_of_numbers(max_number INT)
BEGIN
        DECLARE _min_number INT;
        SET _min_number = 1;
        WHILE _min_number <= max_number DO
                INSERT INTO list_of_numbers SELECT  _min_number;
                SET _min_number = _min_number + 1;
        END WHILE;
END
$$

Armed with this sequence, we can create the following query:

select min(number) from list_of_numbers where number not in (SELECT your_number FROM table1 UNION SELECT your_number FROM table)

SQL - Find the lowest unused number

3 Answers3

Linked