SQL: Select lowest value that doesn't already exist

Question

In TableA I have an int column.

Is it possible using only a select statement to select the minimum value in the column that DOES NOT EXIST and is greater then 0?

For example, if the col has the values 1,2,9 the select statement will return 3. If the col has 9,10,11 it will return 1.

I can achieve this using a temp table or using a loop, but I'm wondering if I can do it using just a select statement?

Answer 1

SELECT MIN(t1.ID+1) as 'MinID'
FROM table t1 LEFT JOIN table t2
On t1.ID+1=t2.ID
Where t2.OtherField IS NULL

Answer 2

SELECT DISTINCT x + 1 "val"
EXCEPT SELECT DISTINCT x "val"
ORDER BY "val" ASC
LIMIT 1

What about this?

Answer 3

select
min(nt.id)
from numbertable nt
left outer join originaldata od
on nt.id=od.id
where od.id is null

have a number table that goes from 1 to your max value (or higher)

Answer 4

SELECT Min(id)
FROM   (SELECT 1 id
        FROM   tablea
        WHERE  1 NOT IN (SELECT id
                         FROM   tablea)
        UNION
        SELECT id + 1 id
        FROM   tablea
        WHERE  id + 1 NOT IN (SELECT id
                              FROM   tablea)) AS min_ids; 

Answer 5

I duplicated my answer from here:

SELECT MIN(a.id) + 1 AS firstfree
FROM (SELECT id FROM table UNION SELECT 0) a
LEFT JOIN table b ON b.id = a.id + 1
WHERE b.id IS NULL

This handles all cases I can think of - including no existing records at all.

The only thing I don't like about this solution is that additional conditions have to be included twice, like that:

SELECT MIN(a.id) + 1 AS firstfree
FROM (SELECT id FROM table WHERE column = 4711 UNION SELECT 0) a
LEFT JOIN table b ON b.column = 4711 AND b.id = a.id + 1
WHERE b.id IS NULL

Please also notice the comments about locking and concurrency - the requirement to fill gaps is in most cases bad design and can cause problems. However, I had a good reason to do it: the IDs are to be printed and typed by humans and we don't want to have IDs with many digits after some time, while all the low ones are free...

Answer 6

try this:(Updated)

    declare @dummy varchar(10)  ;

set @dummy =(select top(1) id from  dbo.b) 

if(   @dummy= '1')
begin
select top(1)l.id + 1 as start
from dbo.b  as l
  left outer join dbo.b  as r on l.id + 1 = r.id
where r.id is null
end
else 
begin
select '1'
end

Answer 7

Give this a try:

declare @TestTable table (
    col int
)

/* Test Case 1: 1,2,9 */
insert into @TestTable
    (col)
    select 1 union all select 2 union all select 9

SELECT MinValue = (SELECT ISNULL(MAX(t2.col),0)+1
                      FROM @TestTable t2 
                     WHERE t2.col < t1.col)
 FROM @TestTable t1
 WHERE t1.col - 1 NOT IN (SELECT col FROM @TestTable)
   AND t1.col - 1 > 0

delete from @TestTable

/* Test Case 2: 9,10,11 */
insert into @TestTable
    (col)
    select 9 union all select 10 union all select 11

SELECT MinValue = (SELECT ISNULL(MAX(t2.col),0)+1
                      FROM @TestTable t2 
                     WHERE t2.col < t1.col)
 FROM @TestTable t1
 WHERE t1.col - 1 NOT IN (SELECT col FROM @TestTable)
   AND t1.col - 1 > 0