SQL - How to retrieve the first free number in a range of numbers

Question

I have a table, TblClient.
The 2 important columns are: ClientID (PK, increment) and ClientNumber.

We can add new clients, and the ClientID is auto assigned, but the ClientNumber can be chosen.

We want to make a "suggestion" for the ClientNumber, but the user can decide to input their own number and not take our suggestion.

The suggestion must be he first available ClientNumber that is bigger than 2200 (first ClientNumber is 2200).

We do this because if a user inputs a manual number, like 5000, when the last inserted one is 2500, we don't wont the next "suggestion" to be 5001, but 2501.

What is the correct way to return this number with a query?

Thank you!

Answer 1

You could address it with a self-left join antipattern, like so:

select coalesce(min(c.ClientNumber) + 1, 2200) first_available_client_id
from tblclient c
left join tblclient c_free on c_free.ClientNumber = c.ClientNumber + 1
where c_free.ClientID is null

Explanation: the left join attempts to combine each record with another record that has the next client number. Then, the where clause filters non-matching joins, which means on records where the next client number is not yet used in the table. Finally, aggregate function min(...) + 1 returns the first available client number. The coalesce() function is there to return 2200 when the table is empty.

For efficiency, you want an index on ClientNumber (it should already be there since you want this column to be unique).

Another option that avoids the use of aggregation is:

select c.ClientNumber + 1 first_available_client_id
from tblclient c
left join tblclient c_free on c_free.ClientNumber = c.ClientNumber + 1
where c_free.ClientID is null
order by c.ClientNumber
limit 1

However this option does not handle the case when the table is empty.