Bob Bob - 6 months ago 20
SQL Question

SQL: Select lowest value that doesn't already exist

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?

Thanks.

Answer
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)