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?

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

How do I get birth control pills?, You may also need to pay for an appointment with a doctor or nurse to get a prescription for the pill. This visit can cost anywhere from $35–$250. But under the  Learn how to do just about everything at eHow. Find expert advice along with How To videos and articles, including instructions on how to make, cook, grow, or do almost anything.

select max(groupid) + 1 from GroupTransactions

Coronavirus Stimulus Package FAQ, But if you receive any of these benefits and do not file a tax return, you'll need to take an extra step if you have qualifying children who will make  If you do not have a copy of your tax return, you may use a Get Transcript self-help tool to get a Tax Return Transcript showing your AGI. Use Get Transcript Online to immediately view your AGI. You must pass the Secure Access identity verification process.

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

What's the Deal With Coronavirus Stimulus Checks, and , You can find out more about how you will get the money by using the IRS' “Filers: Get My Payment” tool. How do I sign up to receive a stimulus  Ask your friends to introduce you to girls they know. Your friends likely know girls they could introduce you to, and this is a common way for people to meet. Get your friends to arrange group outings so you can meet their female friends. Then, talk to them so you can get to know them.

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

Economic Impact Payments, Answers to frequently asked questions about Economic Impact Payments For additional questions regarding the Get My Payment application check out our  New students start here (Applicants, CSU Fully Online, International, Study Abroad) GET asks for a User ID and password in order to sign in. These are based on your 4-digit PIN and your Campus Identification Number (CIN). The User ID for GET is your CIN. Your CIN can be found on your Golden Eagle Card (student ID card).

Economic Impact Payment Information Center, File IRS form 1095-A to get highest federal tax return. Learn what to do, how to file, why you got form, what to do if no 1095-A. How to Request a Refund. Sign in to your Ticketmaster account and select your order. Select “Refund”. Choose the tickets you’d like refunded. Review the details and submit.

How to Use Form 1095-A, Health Insurance Marketplace Statement , President Trump signed a stimulus bill due to the coronavirus outbreak. Most Americans will get a stimulus check, but when and for how much? Do one transaction at checkout through insurance, finish that one, and do a fully new second transaction using the GoodRX code. The pharmacy may also be able to give you a coupon, definitely ask. Such a pain how expensive this shit is, hopefully people calm down and you can get your meds refilled normally in the coming months.

Stimulus Checks: Who Is Eligible and When Will You Get Them?, Make plans to get vaccinated early in fall, before flu season begins. CDC recommends that people get a flu vaccine by the end of October. Getting  Tech support scams are an industry-wide issue where scammers trick you into paying for unnecessary technical support services. You can help protect yourself from scammers by verifying that the contact is a Microsoft Agent or Microsoft Employee and that the phone number is an official Microsoft global customer service number .

Comments
  • Does it has to be correct under concurrency? If two requests ask for the next GroupID, is it OK if both get the same value or should the returned value also be 'reserved' so no other requests can get the same value?
  • There is only 2 users that use this app, so I'm not worried about concurrency.
  • This seems to be the correct solution, however, I checked re-wrote SELECT top 1 n FROM CTE_Numbers num as SELECT n FROM CTE_Numbers num since I wasn't getting my expected answer, and it only came up with the next 100 values. So, from your example, 2001-2101. EDIT: Ok, got it working. My only concern is, what if all the values from 2001-2101 are taken? Does it get the next 100 values after 2101?
  • You're hitting the MAXRECURSION default limit on the CTE numbers list. You can control this via the MAXRECUSION query hint. See the section on this on this link for more. msdn.microsoft.com/en-us/library/ms181714.aspx. I would suggest using the 0 option to remove the limit, then control the end point (4000) by setting it to the max groupid in MyTable + 1, that way the range will always have an availble ID in it.
  • And problem solved. I've never had to use this kind of query before, but now that I know it, I have a feeling I'll be using it more often. Thanks for all your help!
  • @Christian: If you're used to the good old fashioned SQL 2000 way of querying then there are a few things to look into that will make life much easier: CTE tables (as used here), CROSS APPLY, PIVOT/UNPIVOT, OVER/PARTITION BY and more.
  • you need to ensure groupID > 2000
  • obviously, given his data, it would be >2000 anyway, but I'm not sure why he said that...
  • Based on what he's given us, the > 2000 req seemed redundant
  • The reason for the >2000 is that the current DB has values all over the place. They were chosen at random by users prior to the upgrade. Max(groupid) spits back a value in the billions because of the users poor choice of numbering. Which is why "next Available greater than 2000" makes much more sense.
  • So you want to fill in the gaps? I think that is unclear in your question, you might want to edit to clarify that.