Returning the lowest integer not in a list in SQL

Question

Supposed you have a table T(A) with only positive integers allowed, like:

1,1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18

In the above example, the result is 10. We always can use ORDER BY and DISTINCT to sort and remove duplicates. However, to find the lowest integer not in the list, I came up with the following SQL query:

   select list.x + 1
   from (select x from (select distinct a as x from T order by a)) as list, T
   where list.x + 1 not in T limit 1;

My idea is start a counter and 1, check if that counter is in list: if it is, return it, otherwise increment and look again. However, I have to start that counter as 1, and then increment. That query works most of the cases, by there are some corner cases like in 1. How can I accomplish that in SQL or should I go about a completely different direction to solve this problem?

Answer 1

Because SQL works on sets, the intermediate SELECT DISTINCT a AS x FROM t ORDER BY a is redundant.

The basic technique of looking for a gap in a column of integers is to find where the current entry plus 1 does not exist. This requires a self-join of some sort.

Your query is not far off, but I think it can be simplified to:

SELECT MIN(a) + 1
  FROM t
 WHERE a + 1 NOT IN (SELECT a FROM t)

The NOT IN acts as a sort of self-join. This won't produce anything from an empty table, but should be OK otherwise.

Answer 2

SQL Fiddle

select min(y.a) as a
from
    t x
    right join
    (
        select a + 1 as a from t
        union
        select 1
    ) y on y.a = x.a
where x.a is null

It will work even in an empty table

Answer 3

SELECT min(t.a) - 1
FROM   t
LEFT   JOIN t t1 ON t1.a = t.a - 1
WHERE  t1.a IS NULL
AND    t.a > 1; -- exclude 0

This finds the smallest number greater than 1, where the next-smaller number is not in the same table. That missing number is returned.

This works even for a missing 1. There are multiple answers checking in the opposite direction. All of them would fail with a missing 1.

SQL Fiddle.

Answer 4

You can do the following, although you may also want to define a range - in which case you might need a couple of UNIONs

SELECT x.id+1 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON x.id+1 = y.id 
 WHERE y.id IS NULL 
 ORDER 
    BY x.id LIMIT 1;

Answer 5

You can always create a table with all of the numbers from 1 to X and then join that table with the table you are comparing. Then just find the TOP value in your SELECT statement that isn't present in the table you are comparing

SELECT TOP 1 table_with_all_numbers.number, table_with_missing_numbers.number 
FROM table_with_all_numbers
    LEFT JOIN table_with_missing_numbers 
        ON table_with_missing_numbers.number = table_with_all_numbers.number
WHERE table_with_missing_numbers.number IS NULL
ORDER BY table_with_all_numbers.number ASC;

Answer 6

In SQLite 3.8.3 or later, you can use a recursive common table expression to create a counter. Here, we stop counting when we find a value not in the table:

WITH RECURSIVE counter(c) AS (
  SELECT 1
  UNION ALL
  SELECT c + 1 FROM counter WHERE c IN t)
SELECT max(c) FROM counter;

(This works for an empty table or a missing 1.)