get first occurrence of last changed value of a column for each unique id

Question

How to get first occurrence of last changed value of "sval" column? for id = 22, 71 is the last changed value so wants to fetch first occurence of 71. same way for id = 25, 74 is the last changed value so wants to fetch first occurence of 74.

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

data:

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 1

WITH
cte1 AS ( SELECT *, 
                 ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date` DESC) rn1,
                 ROW_NUMBER() OVER (PARTITION BY id, sval ORDER BY `date` DESC) rn2
          FROM test ),
cte2 AS ( SELECT *, 
                 ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date` ASC) rn3
          FROM cte1
          WHERE rn1 = rn2 )
SELECT id, date, sval
FROM cte2
WHERE rn3 = 1;

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

Answer 2

One method for doing this uses lag() to check for a difference and then chooses the last point where there is a difference:

select t.*
from (select t.*,
             row_number() over (partition by id order by date desc) as seqnum
      from (select t.*,
                   lag(sval) over (partition by id order by date) as prev_sval
            from test t
           ) t
      where prev_sval is null or prev_sval <> sval
     ) t
where seqnum = 1;

Very importantly: This returns the last time there was a change even when the value returns to an already seen value for the id. That is how I interpret your question.