sqlmariadbmariadb-10.6

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

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:

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).

Solution 
  • 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