CODEWITHSUNDEEP
phpmysqlsql
Spence
Spence

Reputation: 35

MySQL query skipping 001 numbers

This is a follow up to my previous question that was answered here - Determine the next number in database query with while loop in php

If I have a product tab

products TABLE
==============
ABC001
ABC002
ABC003
ABC005
==============

and use this 

SELECT SUBSTR(t1.id, 4) + 1 as POSSIBLE_MIN_ID
FROM products t1
WHERE NOT EXISTS (
    SELECT * 
    FROM products t2
    WHERE SUBSTR(id, 1, 3)='ABC' AND SUBSTR(t2.id, 4) = SUBSTR(t1.id, 4) + 1
) LIMIT 1

I get the result of 4. However if I have the table looking 

products TABLE
==============
ABC005
ABC006
ABC007
ABC008
==============

It gives me a result of 9. If I have none in the table it gives me a result of 2 not 1. And if I add the ABC001 in it works fine. Why is that and is there a way to fix it so it picks up the 1 as well? How can I have it work properly without having the ABC001 in there?

Thank!

Upvotes: 0

Views: 112

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

If it's just the biggest used ID plus 1 you are looking for:

select concat('ABC', lpad(coalesce(max(substr(id, 4, 3)), 0) + 1, 3, '0')) as new_id
from products;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

If I understand correctly, you want the first unused 'ABCnnn'. So if 'ABC001' is still available get this, else try 'ABC002' and so on.

One method is to create all codes 'ABC001' to 'ABC999' and then remove the ones already in the table. From these take the least one.

You can use any method to generate your numbers or even have a table containing all allowed codes. Here I use binary math to create the numbers:

select min(code) as new_code
from
(
  select concat('ABC', lpad(num,3,'0')) as code
  from
  (
    select a.x + b.x * 2 + c.x * 4 + d.x * 8 + e.x * 16 + f.x * 32 +
           g.x * 64 + h.x * 128 + i.x * 256 + j.x * 512 as num
    from (select 0 as x union all select 1) a
    cross join (select 0 as x union all select 1) b
    cross join (select 0 as x union all select 1) c
    cross join (select 0 as x union all select 1) d
    cross join (select 0 as x union all select 1) e
    cross join (select 0 as x union all select 1) f
    cross join (select 0 as x union all select 1) g
    cross join (select 0 as x union all select 1) h
    cross join (select 0 as x union all select 1) i
    cross join (select 0 as x union all select 1) j
  ) numbers
  where num between 1 and 999
) codes
where code not in (select id from products);

Apart from this, I'd fix the bad database design. Store 'ABC' separately from the number. And if it's always 'ABC', don't store that string at all.

Upvotes: 2

Related Questions