Question 203773 in Mysql Question posted in
The official MySQL documentation can be found here.

How to find missing seial number of large amount of row in mysql

I need to find missing number from column, serial number always start with FAC-number. below is screenshow enter image description here

I have table more than 100k Records in Tables and I want to find missing serial number.

Expecting Results of above table is :

FAC-00002

There are many records in table so, I wanted that to be more quicker result. Any help would be appreciated.

Tags:

2

Answers


  1. 0

    If only individual serial numbers were missing, you could try to use the window function LAG. For the more general case (with multiple consecutive serial numbers missing), you can use/generate the full list of permitted values. Via an OUTER join, you get the missing values.

    with recursive List(id, DocId) as(
  select 1, 'FAC-00001'
  union all
  select
    id + 1,
    concat('FAC-', right(concat('0000', convert(id + 1, char(5))), 5))
  from List
  where id < (select
                max(convert(right(Reference_Document, 5), decimal))
              from SomeTable
             )
)
select
  l.DocId
from SomeTable s
right join List l
  on s.Reference_Document = l.DocId
where s.Reference_Document is null
;

    You will probably need to adjust the maximum recursion depth.

    See it in action: DB-Fiddle

    Please comment, if and as this requires adjustment / further detail.

    Login or Signup to reply.
  2. 0

    you can use a query like this to find the first missing item.

    SELECT CONCAT('FAC-',LPAD(SUBSTRING_INDEX(a.Reference_Dokument, '-', -1)+1, 5, '0')) as firstMissing
FROM yourTable a
LEFT JOIN yourTable b 
  on b.Reference_Dokument = CONCAT('FAC-',LPAD(SUBSTRING_INDEX(a.Reference_Dokument, '-', -1)+1, 5, '0'))
WHERE b.Reference_Dokument IS NULL;

    sample

    mysql> SELECT * FROM yourTable;
+----+--------------------+
| id | Reference_Dokument |
+----+--------------------+
|  1 | FAC-00001          |
|  2 | FAC-00002          |
|  3 | FAC-00004          |
|  4 | FAC-00005          |
|  5 | FAC-00007          |
+----+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT CONCAT('FAC-',LPAD(SUBSTRING_INDEX(a.Reference_Dokument, '-', -1)+1, 5, '0')) as firstMissing
    -> FROM yourTable a
    -> LEFT JOIN yourTable b 
    ->   on b.Reference_Dokument = CONCAT('FAC-',LPAD(SUBSTRING_INDEX(a.Reference_Dokument, '-', -1)+1, 5, '0'))
    -> WHERE b.Reference_Dokument IS NULL;
+--------------+
| firstMissing |
+--------------+
| FAC-00003    |
| FAC-00006    |
| FAC-00008    |
+--------------+
3 rows in set (0.01 sec)

mysql>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search