Find next free number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Find next free number

Author

Topic

SysMan
Starting Member

4 Posts

Posted - 2003-12-18 : 13:50:00

I have a table with a column of numbers between 0 and 999. The numbers are not consecutive, there are gaps, and not all 1000 numbers are present. So there may be 0-14, 18-31, 40-87 etc. but in random order in the table. Just to make things a little more complicated, there are duplicates, a second field defines whether the number is "active" or "inactive". And some of the number fields are NULL, both "active" and "inactive". The rule is that there can't be duplicate "active" numbers but there can be duplicate "inactive" numbers.

I want to know the first free "active" number. From the above example it would be 15. Any help from the SQL gurus would be appreciated

SysMan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-18 : 14:12:08

Do you have a Sequence table?

SELECT MIN(seq)
FROM Sequence
WHERE seq NOT IN (SELECT Column1 FROM Table1 WHERE Active = 1)

Here's the code from Merkin to generate the Sequence table:


Create Table Sequence(
       Seq int
)

--Generate some data for it
SET NOCOUNT ON
Declare @i int
Set @i = 1
WHILE @i <= 8000
     BEGIN
         Insert Into Sequence Values (@i)
         Set @i = @i + 1
     END
SET NOCOUNT OFF

Tara

SysMan
Starting Member

4 Posts

Posted - 2003-12-19 : 04:16:43

This works great. Very interesting technique.

Thanks for your help.

SysMan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-19 : 08:07:50

alternatively, try this (it might be more efficient and no sequence table needed):

select Top 1 number+1 as NextNumber
from YourTable A
where Active=1 and
not exists (Select Number from YourTable B where A.Number+1 = B.Number and B.Active=1)
order by Number ASC

try both, pick which works best for you.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources