get first occurrence of last changed value of a column [closed]

How to get first occurrence of last changed value of “sval” column?

https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=c980809154d41f2accc9f14d569b48f1

data:

enter image description here

in above picture i wanted to fetch highlighted row.

try:

with LastValue as (
  select t.sval
  from test t 
  order by t.date desc 
  limit 1
)
select t.*
from test t
where t.sval = (select sval from LastValue)
  and t.date > (select max(tt.date) from test tt where tt.sval <> (select sval from LastValue))
order by t.date asc
limit 1;

actually the problem statement is i dont want the group by sval first occurence, instead i want the whatever last changed sval so our example after highlighted rows should be returns for ids (22,25).

Answer

Maybe you can try with something like this:

select t.value into @lastValue
from test t 
order by t.date desc 
limit 1;
    
select t.*
from test t
where t.value = @lastValue
  and t.date > (select max(tt.date) from test tt where tt.value <> @lastValue)
order by t.date asc
limit 1;

You can test on this db<>fiddle