so, i have records in my database:

as result i would like to get those values, where in next one 'lvl' is other then in previous one. what i mean, i would like to get as results records with id :

0 - because its the first one,

1 - because lvl is not the same as 0,

2,3 - skip, because lvl is the same as 1

4 - because lvl is not the same as 1,

5 - skip, because lvl is the same as 4,

6 - because lvl is not the same as 5,

7 - because lvl is not the same as 6,

8 - because lvl is not the same as 7,

9 - skip.

CodePudding user response：

This seems ideally suited to lag analytic function (using MySQL 8 ):

with keep as (
  select *, if(id=Min(id) over() or level != lag(level) over(order by id), 1, 0) keepme
  from t
)
select id
from keep
where keepme=1;

Example Fiddle

CodePudding user response：

For MySQL 5.7 and below which support stored routines, we can try using a cursor to compare the numbers and a temporary table to record the findings, all included in a stored procedure. Here you go:

delimiter //
drop procedure if exists test_sproc//
create procedure test_sproc()
begin
declare num int default -1;
declare c_id int ;
declare c_level int;
declare fin bool default false;
declare c cursor for select id, `level` from yourtable;
declare continue handler for not found set fin=true; 

drop temporary table if exists testtb;
create temporary table testtb(id int,`level` int);

open  c;

lp : loop
if fin=true then
leave lp;
end if;

fetch c into c_id,c_level;
if num !=c_level then
set num= c_level;
insert into testtb values(c_id,c_level);
end if;

end loop lp;
end //