insert - mysql update line according to data from next line -

insert - mysql update line according to data from next line -

i have table in mysql:

name id b1 1 b2 1 b3 1 b4 2 b5 2 b6 2 a1 3 a2 3 a3 3

i'd add together info column, notifying id alter @ next line. this:

name beforechange id b1 no 1 b2 no 1 b3 yes 1 b4 no 2 b5 no 2 b6 yes 2 a1 no 3 a2 no 3 a3 no 3 a4 no 3

is there way in sql? thanks!

ugly sin & far ideal efficiency point of view seems work:

create table mytable (id int(10) unsigned not null,name varchar(10)); insert mytable (id,name) values (1,'b1'); insert mytable (id,name) values (1,'b2'); insert mytable (id,name) values (1,'b3'); insert mytable (id,name) values (2,'b4'); insert mytable (id,name) values (2,'b5'); insert mytable (id,name) values (2,'b6'); insert mytable (id,name) values (3,'a1'); insert mytable (id,name) values (3,'a2'); insert mytable (id,name) values (3,'a3'); insert mytable (id,name) values (3,'a4'); select a.id, case when b.id null 'no' when b.id = (select max(id) mytable) 'no' -- < handle lastly line of results set else 'yes' end beforechange,a.name -- rank within id ( select mt.id,mt.name, case mt.id when @curid @currow := @currow + 1 else @currow := 1 , @curid := mt.id end rank mytable mt bring together (select @currow := 0, @curid := -1) r order id,name asc ) left outer bring together -- max rank id ( select t.id,max(t.rank) maxrank ( select mt.*, case mt.id when @curid @currow := @currow + 1 else @currow := 1 , @curid := mt.id end rank mytable mt bring together (select @currow := 0, @curid := -1) r order id,name asc ) t grouping t.id ) b on a.id = b.id , b.maxrank = a.rank;

mysql insert sql-update