How to get first occurrence of last changed value of “sval” column?
in above picture i wanted to fetch highlighted row.
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).
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