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:-

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


Comments

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

rewrite - Trouble with Wordpress multiple custom querystrings -