SQL: Select MIN value that dosnt already exist

Question

Hi there,

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 can I do it using just a select statement

thanks

Answer 1

Hello!

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

What about this?

Answer 2

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 3

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 4

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

Answer 5

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)