Anilrgowda

I am attempting to create a query which will find the first missing number in a sequence of number in a database. For example, I have a field that may have the numbers 1-10, missing 11, have 12-15, missing 16, etc.

What I want to do is go through the table and show that 11 is the first number missing out of the sequence. This is for an application which will issue employee numbers in numerical order, but in the past they were issued seemingly at random so I am trying to clean it up and issue all of the missing numbers. So each time the query is run, it will find the next lowest missing number.

I have thought of iterating through all of the numbers (1- 100,000) but I can only think of doing that by opening and closing the recordset for each number I look up in a For... Next loop. I would rather be able to do this without having to close and re-open the recorset.

------------------

Anilrgowda

Select Top 1 YourNumber + 1 from YourTable T1
where not exists
(select * from YourTable T2 where T2.YourNumber = T1.YourNumber + 1)
order by YourNumber asc

------------------