Displaying next available unused number

[willit]

I have a table that has customer numbers that are manually entered from a form. The numbers are not sequential (in other words there are holes). On the form I want to display the next available unused number. So if the numbers already used are 1, 2, 3, 6, then I want to recommend the user to enter a 4 for the next customer number.

 

[swampBoogie]

Why?

Use a counter column instead.

 

[willit]

Can't, I inherited this database and there are already hundreds of records.

 

[r937]

> "So if the numbers already used are 1, 2, 3, 6,
> then I want to recommend the user to enter a 4
> for the next customer number.

may i ask why?

why are the users even entering a customer number?

let them enter the customer, and let the database decide the number

as for "re-using" a "hole" number, i can think of no benefit for doing this, and several reasons not to


rudy

http://r937.com/

 

[jiqjaq]

If this is what you really want, for whatever reason, this is how you do it:
SELECT MIN(CustNum) + 1 as Target
FROM TableX
WHERE CustNum + 1 NOT IN (SELECT CustNum From TableX)

This will only find the first number available which is greater than the lowest value already in the table. If the lowest value is 2, the number 1 will not be selected. If the table is empty, nothing will be found.

 

[willit]

The boss wants to keep the customer number limited to three digits and some customers have had the same number for years and dont want to change. So I have been given the task of finding a way to use the "holes".

JigJag,
Your SQL seems simple enough but I cant see how it returns a hole. Can you run through an example for me?

 

[willit]

I forgot to tell you JigJag that the query worked and thanks but I still dont understand why.

 

[MichaelRed]

the sql statement will only work properly in a single user (data entry) app. Concurrency issues can (therefore WILL) arise in a multiuser situation. The "three" number restriction imposed is somewhere between foolish and worse. It reflects a 'limiting' approach in many ways. What 'business' can exist over a long period of time with only 999 customers? How will you reliably distinguish the 'relacement' customer #4 from the (perhaps multiple) predecessor csutomer #4's?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jiqjaq]

The query works because, like most things with programming, you need to think of alternative ways of describing what you want to achieve. SQL queries against the data that you have in the tables, not what you don't have. If you need to determine if a value is not in the table, you can only compare it against what's already there.

That's why you have to use expressions like MIN(CustNum) + 1. The + 1 part may not be in the table, but CustNum is. The "holes" you're looking for are really numbers in a sequence. If x is in the table, but x+1 is not, then x+1 is a hole.

Since you want the first missing number, you need the MIN fucntion to check for the lowest field value in the entire table which matches the condition. To verify that x+1 is actually missing, i.e., the condition, you must check it against every value in that field. That's where the NOT IN (Select...) comes in.