Returning a low integer not to a list in SQL

Question

Suppose you have a table T(A)with valid only positive integers, for example:

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

In the above example, the result 10. We can always use ORDER BYit DISTINCTto sort and delete duplicates. However, to find the smallest integer not on the list, I came up with the following query SQL:

   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 starts with counterand 1, check if this counter is in list: if there is one, return it, otherwise increase and look again. However, I have to run this counter as 1, and then increment. This query works in most cases, since there are some angular cases, for example, in 1. How can I do this in, SQLor do I need a completely different direction to solve this problem?

Answer 1

Because SQL runs on sets, the middleware SELECT DISTINCT a AS x FROM t ORDER BY ais redundant.

, , 1 . .

, , :

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

NOT IN . , .

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

Answer 3

, - 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 4

1 X . TOP SELECT, ,

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 5

( 1) , . (.. ), + 1.

select coalesce(min(number),1) from (    
    select min(cnt) number
    from (
        select 
            number, 
            (select count(*) from (select distinct number from numbers) b where b.number <= a.number) as cnt
        from (select distinct number from numbers) a
    ) t1 where number > cnt
    union
    select max(number) + 1 number from numbers
) t1

http://sqlfiddle.com/#!7/720cc/3

Answer 6

SQLite 3.8.3 . , :

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 is missing 1.)

Answer 7

Another method using EXCEPT this time:

SELECT a + 1 AS missing FROM T
EXCEPT
SELECT a FROM T
ORDER BY missing
LIMIT 1;

Answer 8

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-less number is not in the same table. This missing number is being returned.

This works even for absence 1. In the opposite direction, several answers are checked. All of them will fail with the missing 1.

SQL Fiddle