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