CODEWITHSUNDEEP
mysql
Casado cMS
Casado cMS

Reputation: 31

How to find serial numbers without gap in mysql?

Consider a table productSerialnumber with a column serialnumber. this column takes a single serial number of a Product.

Product A has a serial number from 101 to 109 and then 111 to 119 and 139 to 150. the serialnumber 110 and 120-138 for example are not availabel

I would like to have a query or something that can return continous serialnumber set. for example the result will be

from       to 
=======    ====
101        109
111        119
139        150

to be considered is that table has a huge set of data more then a million rows.

Any help will be really appriciated

Upvotes: 3

Views: 254

Answers (2)

Menelaos
Menelaos

Reputation: 26279

This is very fast in my opinion since we avoid joins and only iterate through the data once. The only bottleneck is that we use 2 temporary tables, and need to use user defined values.

Without set statement

select START_INTERVAL+0, END_INTERVAL+0
from
(
  select 
  if(@start = NULL or @start > PRODUCTSERIALNUMBER, @start='', '') as SET_START,
  if (@start = '', @start:= @previous, @start) as START_INTERVAL,
  if(PRODUCTSERIALNUMBER - @previous > 1, concat(@end:=@previous,@start:=''), @end:='') as END_INTERVAL,
  @previous:= PRODUCTSERIALNUMBER as PRODUCTSERIALNUMBER
  from 
  (
    select min(PRODUCTSERIALNUMBER)-2 as PRODUCTSERIALNUMBER from Product
    UNION
    (select PRODUCTSERIALNUMBER as PRODUCTSERIALNUMBER from Product ORDER BY productSerialnumber)
    UNION
    select max(PRODUCTSERIALNUMBER)+2 as PRODUCTSERIALNUMBER from Product    
  ) as TEMP
)
as RESULTS where 
not START_INTERVAL is null AND
not END_INTERVAL is null AND
not END_INTERVAL = '' AND
not START_INTERVAL - END_INTERVAL > 0;

With Set Statement

set @start='';

select select START_INTERVAL+0, END_INTERVAL+0
from
(
  select 
  if (@start = '', @start:= @previous, @start) as START_INTERVAL,
  if(PRODUCTSERIALNUMBER - @previous > 1, concat(@end:=@previous,@start:=''), @end:='') as END_INTERVAL,
  @previous:= PRODUCTSERIALNUMBER as PRODUCTSERIALNUMBER
  from 
  (
    select min(PRODUCTSERIALNUMBER)-2 as PRODUCTSERIALNUMBER from Product
    UNION
    (select PRODUCTSERIALNUMBER as PRODUCTSERIALNUMBER from Product ORDER BY productSerialnumber)
    UNION
    select max(PRODUCTSERIALNUMBER)+2 as PRODUCTSERIALNUMBER from Product    
  ) as TEMP
)
as RESULTS where 
not RESULTS.START_INTERVAL is null AND
not RESULTS.END_INTERVAL is null AND
not RESULTS.END_INTERVAL = '' AND
not RESULTS.START_INTERVAL - RESULTS.END_INTERVAL > 0;

SQLFiddle: http://sqlfiddle.com/#!2/a622a/60

Upvotes: 0

Kickstart
Kickstart

Reputation: 21533

Had more of a play:-

SELECT MIN(aFirstSerial), MAX(aLastSerial)
FROM
(
    SELECT @FirstSerial:=IF(productSerialnumber = @LastSerial + 1, IF(@FirstSerial = 0, productSerialnumber, @FirstSerial), productSerialnumber) AS aFirstSerial, 
        @RangeNum:=IF(productSerialnumber = @LastSerial + 1, @RangeNum, @RangeNum + 1) AS aRangeNum, 
        @LastSerial := productSerialnumber AS aLastSerial
    FROM
    (
        SELECT productSerialnumber
        FROM Product
        ORDER BY productSerialnumber
    ) Sub1
    CROSS JOIN (SELECT @PrevSerial:=0, @RangeNum:=0, @FirstSerial:=0, @LastSerial:=0) Sub2
) Sub3
GROUP BY aRangeNum

SQL Fiddle for it here:-

http://sqlfiddle.com/#!2/5cbc2/12

Upvotes: 2

Related Questions