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

Question

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?

Answer 1

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

Answer 2

select max(groupid) + 1 from GroupTransactions