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

user_2374769 0
Viewed 711

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!

mysql sql
3 Answers

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.

user_106767160

You could compare the ClientNumber to the rank of the ClientNumber and then it would be just the case of picking the first row where they don't match. Something like this:

WITH cte AS (
SELECT 
  ClientNumber
  ,RANK() OVER(ORDER BY ClientNumber) + 2199 AS r
FROM t
WHERE ClientNumber >= 2200)
SELECT
  r
FROM cte
WHERE r != ClientNumber
ORDER BY r
LIMIT 1
user_14191750

I would use exists, but you have to be very careful to check for the number being greater than 2200:

select coalesce(min(c.ClientNumber) + 1, 2200) as next_available
from tblclient c
where not exists (select 1
                  from tblclient c2
                  where c2.clientNumber = c.clientNumber + 1 
                 ) and
      c.clientNumber >= 2200;

Very importantly: This works even when the custom client numbers are less than 2200. The other solutions do not seem to take this into account.

user_11440350
Related