CODEWITHSUNDEEP
sql-serversql-server-2008
Christian
Christian

Reputation: 523

How do I get the "Next available number" from an SQL Server? (Not an Identity column)

Technologies: SQL Server 2008

So I've tried a few options that I've found on SO, but nothing really provided me with a definitive answer.

I have a table with two columns, (Transaction ID, GroupID) where neither has unique values. For example:

TransID | GroupID
-----------------
23      | 4001
99      | 4001
63      | 4001
123     | 4001   
77      | 2113
2645    | 2113
123     | 2113
99      | 2113

Originally, the groupID was just chosen at random by the user, but now we're automating it. Thing is, we're keeping the existing DB without any changes to the existing data(too much work, for too little gain)

Is there a way to query "GroupID" on table "GroupTransactions" for the next available value of GroupID > 2000?

Upvotes: 6

Views: 15747

Answers (5)

Jean-Marc
Jean-Marc

Reputation: 109

In my situation I have a system to generate message numbers or a file/case/reservation number sequentially from 1 every year. But in some situations a number does not get use (user was testing/practicing or whatever reason) and the number was deleted.

You can use a where clause to filter by year if all entries are in the same table, and make it dynamic (my example is hardcoded). if you archive your yearly data then not needed. The sub-query part for mID and mID2 must be identical.

The "union 0 as seq " for mID is there in case your table is empty; this is the base seed number. It can be anything ex: 3000000 or {prefix}0000. The field is an integer. If you omit " Union 0 as seq " it will not work on an empty table or when you have a table missing ID 1 it will given you the next ID ( if the first number is 4 the value returned will be 5).

This query is very quick - hint: the field must be indexed; it was tested on a table of 100,000+ rows. I found that using a domain aggregate get slower as the table increases in size.

If you remove the "top 1" you will get a list of 'next numbers' but not all the missing numbers in a sequence; ie if you have 1 2 4 7 the result will be 3 5 8.

set @newID = select top 1 mID.seq + 1 as seq  from  
(select a.[msg_number] as seq from [tblMSG] a --where a.[msg_date] between '2023-01-01' and '2023-12-31'  
 union select 0 as seq ) as mID  
left outer join  
(Select b.[msg_number] as seq from [tblMSG] b --where b.[msg_date] between '2023-01-01' and '2023-12-31'  
) as mID2 on mID.seq + 1  = mID2.seq where mID2.seq is null order by mID.seq  

-- Next: a statement to insert a row with @newID immediately in tblMSG (in a transaction block).  
--   Then the row can be updated by your app.

Upvotes: 0

Jonathan Greenstreet
Jonathan Greenstreet

Reputation: 1

There are always many ways to do everything. I resolved this problem by doing like this:

declare @i int = null 

declare @t table (i int)
insert into @t values (1)
insert into @t values (2)
--insert into @t values (3)
--insert into @t values (4)
insert into @t values (5)
--insert into @t values (6)

--get the first missing number
select   @i = min(RowNumber)
from (
select  ROW_NUMBER() OVER(ORDER BY i) AS RowNumber, i
    from (
        --select distinct in case a number is in there multiple times
        select distinct i
        from @t
        --start after 0 in case there are negative or 0 number
        where i > 0
    ) as a
) as b 
where RowNumber <> i

--if there are no missing numbers or no records, get the max record
if @i is null
    begin
    select @i = isnull(max(i),0) + 1 from @t
    end

select @i

Upvotes: 0

Doug S
Doug S

Reputation: 10315

The following will find the next gap above 2000:

SELECT MIN(t.GroupID)+1 AS NextID
FROM GroupTransactions t (updlock)
WHERE NOT EXISTS
(SELECT NULL FROM GroupTransactions n WHERE n.GroupID=t.GroupID+1 AND n.GroupID>2000)
AND t.GroupID>2000

Upvotes: 1

Jon Egerton
Jon Egerton

Reputation: 41569

I think from the question you're after the next available, although that may not be the same as max+1 right? - In that case:

Start with a list of integers, and look for those that aren't there in the groupid column, for example:

;WITH CTE_Numbers AS (
    SELECT n = 2001
    UNION ALL
    SELECT n + 1 FROM CTE_Numbers WHERE n < 4000 
)
SELECT top 1 n 
FROM CTE_Numbers num
WHERE NOT EXISTS (SELECT 1 FROM MyTable tab WHERE num.n = tab.groupid)
ORDER BY n

Note: you need to tweak the 2001/4000 values int the CTE to allow for the range you want. I assumed the name of your table to by MyTable

Upvotes: 9

Jason
Jason

Reputation: 89129

select max(groupid) + 1 from GroupTransactions

Upvotes: 4

Related Questions