0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
salvatore100
  • 1
  • Could you please give some sample data and mention what you've tried so far interms of coding? – Helio Sep 07 '22 at 09:22
  • `code`[dbo].[Clients] [ID] [bigint] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [Surname] [nvarchar](50) NOT NULL, [CardCode] [int] NULL `code` the table already contains thousands of records and up to now the "CardCode" field was user insertable. CardCode must be unique. Now I need a query that gives me the first "CardCode" available since issue 2000. Mine was a feasibility request. Is it possible to do this with a query? If so, how? – salvatore100 Sep 07 '22 at 09:33
  • Storing all available card numbers in a separate table and marking them if they are used or not might be a good start to keep track of unused numbers for it. There are ways to identify it in a single select but it might raise some performance problems. – Helio Sep 07 '22 at 09:45

3 Answers3

0

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
2000106
3000201
1000101

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 trick.

Hello World
  • 1
  • 2
0

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 )
user19902261
  • 121
  • 5
0

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.

Annosz
  • 992
  • 6
  • 23