Hi all,

I am trying to write a query to do the following in SQL server but am
struggling:

I have a table with a int number field in it and I want to find out
the lowest unused number in the table. Unfortunately, the numbers are
not necesarily sequential (due to deletes). So I may have the
following records:

num
----
1
2
3
4
6
8
9
10

I want a query to return me the value 5 - i.e. the lowest unused int
in the table (assuming counting starts at 1). I know I could do this
using a temporary table containing a full sequence of possible numbers
(given some ceiling) using something like:
select min(num) from temptable where num not in (select num from
maintable)
This would however mean creating a temporary table which I am trying
to avoid. I could also use a cursor to itterate through the records
above to find the lowest unused number but it seems overkill.

I'm sure there must be a way of doing this using a simple (possibly
nested) query. Any ideas?

Thanks in advance

--James

SELECT COALESCE(MIN(T1.num)+1,1)
FROM YourTable AS T1
LEFT JOIN YourTable AS T2
ON T1.num = T2.num - 1
WHERE T2.num IS NULL

--
David Portas
SQL Server MVP
--

David,
Just what I needed - cheers - nice bit of SQL too!

Regards

--James

SELECT MIN(F1.num + 1)
FROM Foobar AS F1
UNION
VALUE (0) -- needs to be SELECT 0 in dialect
WHERE (T1.num +1)
NOT IN (SELECT num FROM Foobar);

This will give you an answer on the high end if the sequence is
complete.