Geek Answers Handbook

How to get "Next Available Number" with SQL Server? (Not Identity Columns)

Technology: SQL Server 2008

So, I tried several options that I found on SO, but nothing gave me a definitive answer.

I have a table with two columns, (Transaction ID, GroupID), where there are no unique values. For instance:

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

Initially, the groupID was randomly selected by the user, but now we will automate it. The fact is that we save the existing database without any changes in the existing data (too much work, for too little gain)

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

+3
3

, , , , max + 1 ? - :

, groupid, :

;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

: 2001/4000 int the CTE, , . , MyTable

+8
select max(groupid) + 1 from GroupTransactions
+2

The following space above 2000 will be found below:

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
+1
source

More articles:


All Articles