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
 General SQL Server Forums
 New to SQL Server Programming
 How to select First Missing Number in Order

Author  Topic 

mkdlmr
Starting Member

21 Posts

Posted - 2014-02-21 : 16:00:17
Hi All,

I am assigning temporary Ids in my table, and right now I am assigning new ones via find the MAX(Temp_Id), stripping out the number and adding one.

Since these are temporary I would like to be able to reuse older ones when they are no longer in the table.

For example I have:
ID0001
ID0002
ID0003
ID0006

My code right now will add ID0007. I would like to re-use ID0004 and ID0005 since they're no longer in use. How do I go about do this?

Thanks,
MK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-21 : 16:13:58
Seems like a lot of processing overhead for no real gain.. but, here are two ways to do that:
DECLARE @Foo TABLE (TempID VARCHAR(20))

INSERT @Foo
VALUES
('ID0001'),
('ID0002'),
('ID0003'),
('ID0006')


-- Method 1
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9),
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT
MIN(T.N)
FROM
Tally AS T
LEFT OUTER JOIN
@Foo AS F
ON T.N = CAST(STUFF(TempID, 1, 2, '') AS INT)
WHERE
F.TempID IS NULL


-- Method 2
SELECT
MIN(CAST(STUFF(TempID, 1, 2, '') AS INT) + 1) AS NewIDNumber
FROM
(
SELECT
*,
LEAD(TempID, 1) OVER (ORDER BY TempID) NextID
FROM @Foo
) AS T
WHERE
CAST(STUFF(NextID, 1, 2, '') AS INT) - CAST(STUFF(TempID, 1, 2, '') AS INT) > 1
OR NextID IS NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-23 : 06:08:28
quote:
Originally posted by mkdlmr

Hi All,

I am assigning temporary Ids in my table, and right now I am assigning new ones via find the MAX(Temp_Id), stripping out the number and adding one.

Since these are temporary I would like to be able to reuse older ones when they are no longer in the table.

For example I have:
ID0001
ID0002
ID0003
ID0006

My code right now will add ID0007. I would like to re-use ID0004 and ID0005 since they're no longer in use. How do I go about do this?

Thanks,
MK



Ideally there's no need to worry about gaps in identity as records will still be present in sequential order and you'll still be able to retrive them in same order using order by. So I would say it would be a kind of overkill to have logic for reusing identity gaps unless you've been enhausted for new id values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-24 : 12:32:09
Duplicate post. See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=191509


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -