SQL Server : check not existing number into a table-CodePudding

I have a Clients table already populated by thousands of records and now I need to search for a non-existing number in the card number column starting from the number x.

Example: I would like to search for the first available card number starting from number 2000.

Unfortunately I cannot select MAX() as there are records with 9999999 (which is the limit).

Is it possible to do this search through a single SELECT?

CodePudding user response：

It's possible with a few nested SELECTs:

SELECT MIN(`card_number`)   1 as next_available_number
  FROM( SELECT (2000-1) as `card_number`
        UNION
        SELECT `card_number`
        FROM clients
        WHERE `card_number` >= 2000
      ) tmp
  WHERE NOT EXISTS ( SELECT NULL
                   FROM clients
                   WHERE `card_number` = tmp.`card_number`   1 )

CodePudding user response：

It can be done with a self-join on your clients table, where you search for the lowest cardnumber for which the cardnumber 1 does not exist.

In case x is 12, the query would be:

SELECT MIN(cardnumber)   1
FROM clients
WHERE cardnumber   1 NOT IN (SELECT cardnumber FROM clients)
  AND cardnumber   1 > 12

Eg. with a dataset of

INSERT INTO clients (cardnumber) VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(11),(12),(13),(14),(15),(17),(18)

this returns 16, but not 10. Example on SQL Fiddle.

I think this is very similar to this question, but the minimum criteria is new.

CodePudding user response：

If the credit card is represented as integer in your table and your starting number is 2000 you could do something like:

SELECT top 1 (card_id   1) 
FROM CreditCards t
WHERE card_id IN (
    SELECT card_id
    FROM CreditCards
    WHERE card_id LIKE '%[2][0][0][0]%'
)
AND NOT EXISTS (SELECT 1 FROM CreditCards t2 WHERE t2.card_id = t.card_id   1)
ORDER BY card_id

Example data (Table: CreditCards):

card_id
2000002
2000103
2000000
2000108
20001006
3000201

Result is: 2000001

Note that %[2][0][0][0]% is fixed here. You could also introduce a parameter.

It is not an optimal solution, but it does the work.