get next number when gaps in records

Question

get next number when gaps in records

jane47

Old Hand

Points: 376
More actions
December 2, 2010 at 9:12 pm

#143748

hi
I have a table that contains records that identified by a number. When a new record is inserted, I need to generate the next number in sequence. (was going to do 'select max(number) from....' however there are gaps in the number and so I need to find one of these gaps and use a number from there
eg there are 4 records in the table with the numbers 74,75,80,81. I need to add a new record with the number 76.
How do I find the number 76??:-)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

jane47 (12/2/2010)
hi
I have a table that contains records that identified by a number. When a new record is inserted, I need to generate the next number in sequence. (was going to do 'select max(number) from....' however there are gaps in the number and so I need to find one of these gaps and use a number from there
eg there are 4 records in the table with the numbers 74,75,80,81. I need to add a new record with the number 76.
How do I find the number 76??:-)

I would say that's a bad idea. How would you handle the situation when you roll over into the existing records? (In this case, once you start off from 76, how would you handle the record with the number 80?)

Secondly, why would you want to resuse a number which has already been issued once?

Finally, if the column is an identity column, you will not be able to insert the same value again, provided IDENTITY_INSERT is OFF (default).

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

WayneS SSC Guru Points: 95460 More actions · Answer 2

I got this sweet little bit of delightful SQL code from MVP Jeff Moden:

DECLARE @test-2 TABLE (ID INT PRIMARY KEY CLUSTERED);

INSERT INTO @test-2

SELECT 74 UNION ALL

SELECT 75 UNION ALL

SELECT 80 UNION ALL

SELECT 81;

--===== Find the "gap ranges" --This takes 3 seconds on my 8 year old machine

-- Finds trailing edge of "islands" and then computes the gaps

-- This assumes that gaps include any whole number greater than 0

SELECT GapStart = ISNULL((SELECT MAX(lo.ID+1)

FROM @test-2 lo

WHERE lo.ID < hi.ID),1),

GapEnd = hi.ID - 1

FROM @test-2 hi

WHERE hi.ID NOT IN (SELECT ID + 1 FROM @test-2)

AND hi.ID > (SELECT MIN(ID) FROM @test-2);

--AND hi.ID > 1; -- if you consider it a gap if 1 should be present

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

jane47 (12/2/2010)
hi
I have a table that contains records that identified by a number. When a new record is inserted, I need to generate the next number in sequence. (was going to do 'select max(number) from....' however there are gaps in the number and so I need to find one of these gaps and use a number from there
eg there are 4 records in the table with the numbers 74,75,80,81. I need to add a new record with the number 76.
How do I find the number 76??:-)

This is always a problem and can be a source of deadlocks, as well. Considering how big INT and BIGINT actually are and how bullet-proof IDENTITY columns are, why do you need to use/reuse numbers that have gaps in them?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jane47 Old Hand Points: 376 More actions · Answer 4

I need to use the numbers because my customer initially blocks out a range of numbers, then adds records using these blocked numbers, or the next available number in the system (depends on the option selected) and then later on, any numbers not used in the range can be released and are then able to be used as the next available number in the system.

eg blocked range 40-50.

records are added for 40 to 45 also 51 and 52

numbers 46-50 are then released.

so the next number that should be used is 46 not 53. 53 would only be used once 46-50 have be used.

jane47 Old Hand Points: 376 More actions · Answer 5

I was planning to have a function that worked out if there was a gap in the numbers eg 76, then use that. I just posted an explanation of why I have gaps in my numbers.

I need to know how to find the next number whether its a gap or not

jane47 Old Hand Points: 376 More actions · Answer 6

jane47

Old Hand

Points: 376

December 2, 2010 at 11:42 pm

#1258227

thanks WayneS - just what I was looking for

WayneS SSC Guru Points: 95460 More actions · Answer 7

jane47 (12/2/2010)
thanks WayneS - just what I was looking for

I'm glad it's working for you.