How to find serial numbers without gap in mysql? -
consider table productserialnumber column serialnumber. column takes single serial number of product.
product has serial number 101 109 , 111 119 , 139 150. serialnumber 110 , 120-138 example not availabel
i have query or can return continous serialnumber set. example result be
from ======= ==== 101 109 111 119 139 150
to considered table has huge set of data more million rows.
any appriciated
had more of play:-
select min(afirstserial), max(alastserial) ( select @firstserial:=if(productserialnumber = @lastserial + 1, if(@firstserial = 0, productserialnumber, @firstserial), productserialnumber) afirstserial, @rangenum:=if(productserialnumber = @lastserial + 1, @rangenum, @rangenum + 1) arangenum, @lastserial := productserialnumber alastserial ( select productserialnumber product order productserialnumber ) sub1 cross join (select @prevserial:=0, @rangenum:=0, @firstserial:=0, @lastserial:=0) sub2 ) sub3 group arangenum
sql fiddle here:-
Comments
Post a Comment