On Mon, Jul 22, 2013 at 7:32 PM, Sathia S <[email protected]> wrote:
> I think we cant do it in mysql. Because of string 'S'. > But found some link if id is number > http://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table > > But we can do it with some scripting language like ruby or python to solve > the issue. > > Getting all ids and generate ids of array from (S001.. S00N) and compare > those array. May be its not best solution. But we can achieve through some > scripting language. > > > On Mon, Jul 22, 2013 at 7:21 PM, Siva prabu <[email protected]> wrote: > > > My table:- > > > > +-------+ > > webno > > -------+ > > > > |S001| > > |S002| > > |S003| > > |S005| > > |S006| > > |S009| > > |S010| > > > > > > How to find the missing numbers in the Column webno for the series....... > > > > > > i need like > > > > > > ****** > > S004 > > S008 > > > > ****** > > > > > > ** > > > > * regards, * > > > > *| Sivaprabu. G **|* > > > > Like we can create a virtual table and compare, select cast(concat('S',lpad(sno,3,'0')) as char(6)) as missing from (SELECT @row := @row + 1 as sno FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2,(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=0) as b where @row<1000) as a where a.sno not in (select distinct b.no from (select b.*,if(@mn=0,@mn:=b.no,@mn) as min,(@mx:=b.no) as max from (select a.webno,substring(a.webno,2,4)+0 as no from web as a) as b,(select @mn:=0,@mx:=0) as x order by no) as b) and a.sno between @mn and @mx; Any drawbacks in this? Thanks Raju _______________________________________________ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc ILUGC Mailing List Guidelines: http://ilugc.in/mailinglist-guidelines
