Share This!

Friday, July 6, 2012

MSSQL: Find the next available number in a sequence

 So I have this data table, with a non-key column, and I need to write a SQL Query to find the next unused number in the sequence that is greater than a certain starting number.

It's like this:
A long time ago, the table started somewhere around 1000.  This is not a key column, and some of the older records have been archived out of the table.  So I have a weird number that will serve as my minimum.   BUT! there are also some wacky big numbers in the system and I don't want to do a max(n)+1.

Here's a beakdown:

0-35956these numbers are there, with lots of missing numbers and gaps due to archiving.
35957-1904121900a few of these are there, I want to find the next available number in this region.
1904121901 and upI want to ignore these numbers, and not use them (until all the numbers leading up to here are filled).

My Goal here is to create a SQL Query I can run once to find the next available number (meaning the lowest number above 35957 that does not exist in the column).

Here is what worked.

select min(Item_No)+1 as NextID
from Data_Table DT
where not exists
(select 1 from Data_Table DT2 where DT2.Item_No=DT.Item_No+1)
and Item_No > 35956


Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

No comments:

Post a Comment

Contact Us


Email *

Message *