Bob Bob - 4 months ago 17x
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?


from numbertable nt
left outer join originaldata od
where is null

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