Select first unused number in a range between 00 and 99


I have a table called Operators, for some obscure reason that defies logic, a numeric field called OperatorID of type Long Integer has been throttled by a business policy, stating that "there shall never be more than 100 operators" hmmmm.

The throttling is in the form of an input mask '00' and a format of '00' being applied at table level indexed(no duplicates).

The Problem is this:
I need to assign a new OperatorID, now operatorID's 01, 02 and 03 have been assigned, as have 99, 98, 97.

I need some SQL script that will return the first available number in the range 1 - 100 that has not been consumed.

Hope that is clear enough for you, if not, let me know, and I will attempt to clarify.

Dont really want to use a function(builtin or custom) for this if at all possible, want it in SQL script please.

Regards Alan
LVL 26
Alan WarrenApplications DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Only SQL, i dont think so.

Public function getID() as integer

Dim db as dao.database
Dim rs as dao.recordset
Dim lLast as long

Set db = currentdb
Set rs = db.openrecordset("Select OperatorID from Operators")

lLast = rs(0).Value

While not rs.EOF
   If rs(0).value - 1 <> lLast then
        getID = lLast + 1
   End if
 lLasr = rs(0).value


End function

You can use it in SQL like

INSERT INTO operators ( operatorID, otherfield ) VALUES " & getID " & 'myValue'"


should be

"INSERT INTO operators ( operatorID, otherfield ) VALUES ( " & getID " & 'myValue' )" (in VB)

or in SQL builder

INSERT INTO operators ( operatorID, otherfield ) VALUES ( getID(), "myValue");
Do you have your range in a table? This may sound clunky but I would use a table that contains the whole range of operator IDs as well as the operator name, then you can easily build an SQL that finds the first unused ID.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

I was thinking along the lines of having a table holding values 1 to 100

then u can do something like this

select top 1 num from lookupnumberstable where num not in (select operatorid from operators) order by num

Alan WarrenApplications DeveloperAuthor Commented:
Hi Dave :)
how ya going mate?

This has to be do-able, without temp tables and without a vba function.
never say never.

Using a temp table that contains the range we could:

  Select Top 1 NumField from tblRange where Numfield Not In(Select OperatorID From Operators) From tblRange

Was hoping not to create temp table, MDE.

damn, too slow, all these posts been added, should of refreshed!
anyway looks like Will has the same idea as me but has beat me to it

Morning Dave,
why not to create a query in your table Operators adding a counter agaisnt

counter    OperatorID
1                 1
2                 2
3                 4 -------->> Here you wouls see the diff
4                 5

Then select top as rocki mentioned. All this you could do in the same query or using 2 queries.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alan WarrenApplications DeveloperAuthor Commented:
Think I got it guys!

SELECT TOP 1 [OperatorID]+1 AS NextID FROM Operators
WHERE ((([OperatorID]+1) Not In (select OperatorID From Operators) And ([OperatorID]+1)<100))
ORDER BY [OperatorID]+1;

Thanks anyway.
wow, it works. Ive tried myself.

Very very good alan :)
Alan WarrenApplications DeveloperAuthor Commented:

I read your post after I posted my "I got it guys" post.
Exactly what I did, good one friend, thank you.

hey thanks for the points Alan :)

but you did it !!

again, very good !

Alan WarrenApplications DeveloperAuthor Commented:
Your logic was good Álvaro.

Instead of selecting what was there, select what wasn't there; by adding one and filtering unmatched on what was there.

good one

Long time no see mate.  How's things going down in  Adelaide.  You been busy with work??


Ill be going for an adventure to the UK for about 6 months {i here you Europeans have transport problems too} next February, ill have to meet up with you somewhere and buy you that Kronie.

Keep up the good work guys, i guess my "love" of VB gets the better of me...

Alan WarrenApplications DeveloperAuthor Commented:

Hi Dave,
things is pretty good here, eeking out a megre existance contracting, sick of this long winter.
Hows them traffic control algorithm's going, can you arrange me a clear run through Brisbane in late September?
Got a strong affinity with VBA too, but I always prefer to solve it with SQL if possible, its easilly preserved as serverside procedure. To be honest mate, it ended up being an ADO/SQL/VBA function anyway.

Function mGetNextOperatorID() As Long

  ' aw 6-aug-2004
  ' Function to generate new OperatorID
  Dim lngNextOperatorNumMax As Long
  Dim sql As String
  Dim sWhere As String
  Dim sOrderBy As String
  Dim rs As ADODB.Recordset
  Set rs = New ADODB.Recordset
  sql = "SELECT TOP 1 [OperatorID]+1 AS NextID FROM Operators"
  sWhere = " WHERE ((([OperatorID]+1) Not In (SELECT OperatorID FROM Operators) And ([OperatorID]+1)<100))"
  sOrderBy = " ORDER BY [OperatorID]+1"
  sql = sql & sWhere & sOrderBy
  rs.Open sql, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  With rs
    If Not .BOF And Not .EOF Then
      lngNextOperatorNumMax = .Fields("NextID")
      lngNextOperatorNumMax = 0
    End If
  End With
  Set rs = Nothing
  mGetNextOperatorID = Format(lngNextOperatorNumMax, "00")  '  Format is redundant
End Function

Take care mate, stay in touch, maybe catch you when Im in town.


I only just realised that it was you how asked the Q.  I would have said hello earlier.

I'm still stick with A97 at work, hence the DAO, plus i wanted to get my train go home, and didnt really want to play with SQL.

Clean run in Brissie, good luck, unless you are driving at 2am and you're lucky.

Take care buddy,

Dave, transport problems magnified in the UK, especially these damn trains! Im fed up with commuting!

feb's gonna be cold, good idea to meet up,
been damn hot last two days, yesterday I spent all day lazing on the grass, at the cricket ground with bbq and loads of beer, no krony but stella was there. I was totally shagged by the end of the day, heat and beer!
>> transport problems magnified in the UK

Glad to hear (for my sake)

>> feb's gonna be cold

Yeah i know, and it sickens me.

>> at the cricket ground with bbq and loads of beer

You an Aussie mate, that's my perfect summer Sunday.

>> heat and beer

It kills everyone, but they go thogether soooo well.

My folks are moving over there soon (just sold their house over here) so im going to bum with them for a while, cheap accom, cant complian with that.

Catch you both later.

>> feb's gonna be cold

and august too. I havent seen a bloody sunny day this summer yet.  
It sounds like its cold 24/7/365.  Does it get over 30C??  I used to live in Townsvile (north Qld - Australia) and 35 was "cool" for summer.  30C wasn't unusual for "winter".
its been damn hot last weekend, temp about 26 to 28C
now its just chucking it down, so much rain, but still warm

Dave, sorry for dissapoint you, but 30C!!! you are joking !! I've been over 2 years here and maybe its gone over that, once or twice. I used to live in Alicante (Spain) and i think they are having 35 average.

Now, im getting used to it.
Well, true, last weekend was good maybe best of the year, nearly 30C!
Dave, warmest months August-September (mid)

Its hot enough to merit putting on the AC in the car, otherwise your messed!

Occasionally July and June, even May
Decent weather in April, though it has snowed once, that was a freak
starts getting cold October, gets better after feb

from my experience, south of England is generally a little warmer than the north. But then maybe it depends on whereabouts you are.

Bring your winter woolies, not the aussie ones, t-shirt and shorts but the british ones
>> 26 to 28C

Hot?? my @$%#%@!!!!

That's cold dude!!!

Its winter and gets up to 25+ most days during the day ( below 5 is really cold at night, and thats cold......brrrrrr.... - considering no-one has heating or anytihng, only the "rich" have air-con, and thats only used during summer, even if it is reverse cycle {i havent used mine at all since Feb - summer} ) (({{}{{{{{{[[[{ cheese }]]]}}}}}{}]))
thats why the assuie cricket team only come in the english summer, cant stand the cold
now play the english in cold conditions, perhaps the english may win the ashes for once!
>> for once!

...... good luck, they'll need more than that!

Im off to bed, tooo many beers ( public holiday tomorrow, its after 11pm ).  Had a few too many brews at a mates house after work.

Catch you all later.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.