after spending considerable time (thanks abecee) in creating sub-queries sort table find dismay although works , rapidly in mysql workbench , other tools there known issues sub queries when running mysql queries in java (in jsp). not possible change jdbc connect string appreciate information on whether possible modify following code use joins or something. note had encapsulate 3 variables in first select otherwise mysql in java throws syntax error.
i sql newbie struggling this.
select @locationid:=0 locvar, @ts:=null tsvar, @changed:=0 changed, l4.assetid, l4.node, a.name name, l.zonename zone, sec_to_time(duration) duration (select min(assetid) assetid, min(nodeid) node, sum(secdiff) duration, changed (select assetid , nodeid , @changed := if(nodeid <> previouslocationid, @changed + 1, @changed) changed , ifnull(timestampdiff(second, previousts, ts ), 0 ) secdiff (select assetid , nodeid , @locationid previouslocationid , @locationid := nodeid currentlocationid , ts , @ts previousts , @ts := ts currentts logs l1 order ts ) l2 order ts ) l3 group changed ) l4 join asset on l4.assetid = a.id join node l on l4.node = l.id duration != 0 order changed desc thanks. active.
Comments
Post a Comment