Smallest positive number not already used. 1

[donutman]

What's a "good" way to obtain the smallest positive number not already used within a particular integer field of a table? I'm running MS SQL Server.

 

[nigelrivett]

select min(i+1)
from tbl t1
where not exists (select * from tbl t2 where t1.i = t2.i+1)

select top 1 t1.i+1
from tbl t1
left join tbl t2
on t1.i = t2.i+1
where t2.i is null
order by t1.i

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[donutman]

A slight typo in nigelrivett's solution. It should read:

select top 1 t1.i+1
from tbl t1
left join tbl t2
on t1.i+1 = t2.i
where t2.i is null
order by t1.i

 

[nigelrivett]

Oops - thanks

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[emblem]

why not just

select max(i)+1 from tbl

if tbl is big and there is an index on the i column, then this might be quicker:

select top 1 i + 1 from tbl
order by i

Regards to all,
Emblem