MySQL: Extract the date when a value in a column has changed

Question

I have a table in a mysql database that tracks changes in settings. Looks like this:

date	setting 1	setting 2
2021-01-02	0.01	0.00
2021-01-03	0.02	0.00
2021-01-04	0.03	0.00
2021-01-05	0.04	0.00
2021-01-06	0.04	0.01
2021-01-07	0.03	0.01
2021-01-08	0.04	0.01
2021-01-09	0.04	0.02
2021-01-10	0.05	0.02
2021-01-11	0.06	0.02
2021-01-12	0.06	0.00
2021-01-13	0.07	0.00
2021-01-14	0.08	0.00
2021-01-15	0.08	0.01

I'm looking to get all dates on which 'Setting 2' has changed. Expected result:

date	setting 1	setting 2
2021-01-02	0.01	0.00
2021-01-06	0.04	0.01
2021-01-09	0.04	0.02
2021-01-12	0.06	0.00
2021-01-15	0.08	0.01

Any suggestions please?

You could take a look at the `LAG` function. That could be used to get what you need. — Reinis Verbelis, Sep 10 '21 at 07:25
@esqew I am not looking for free code, just ideas how this can be achieved. Sorry if I didn't include enough details in the post. — user15005160, Sep 10 '21 at 09:11
@ReinisVerbelis I looked into LAG but I am unclear how to show only rows where the value in setting2 changes. — user15005160, Sep 10 '21 at 09:11
@Stu some older one, 5 I think. It doesn't support window functions which I guess could be used. Still, I'd accept a window function solution as an answer because I'd like to learn the technique. — user15005160, Sep 10 '21 at 09:11

score 1 · Accepted Answer · answered Sep 10 '21 at 09:42

Run:

SELECT `date`,setting1,setting2 FROM  ( SELECT  *,
                        IF(@prevStatus = s.setting2, @rn := @rn + 1,
                        IF(@prevStatus := s.setting2, @rn := 1, @rn := 1) ) AS rn
                FROM setting s
         CROSS JOIN 
                (
                 SELECT @prevStatus := -1, @rn := 1
                ) AS var 
        ORDER BY s.`date`
               ) AS t
        WHERE t.rn = 1
        ORDER BY t.`date` ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/1

All credential to user: @1000111 : Mysql select row when column value changed

score 1 · Answer 2 · answered Sep 10 '21 at 10:14

As you are interested in a window function solution, this will work in MySQL 8, or any platform that supports ansi window functions.

select date, setting1, setting2 from (
    select *, case when Lag(setting2,1,-1) over(order by date) != setting2 then 1 end changed
    from t
)t
where changed=1

score 1 · Answer 3 · answered Sep 10 '21 at 11:11

In MySQL 8+, you can use lag(). I would phrase this as:

select t.*
from (select t.*,
             lag(setting2) over (order by date) as prev_setting2
      from t
     ) t
where not prev_setting2 <=> setting2;

Note that <=> is the null-safe comparison operator.

If you are using an older version of MySQL, well, at this point I would recommend that you upgrade.

hongnhat · Answer 4 · 2021-09-10T09:49:31.513

0

my solution

SELECT date,setting1,setting2 FROM 
( SELECT *,
  IF(@prevStatus = YT.setting2, @rn := @rn + 1,
    IF(@prevStatus := YT.setting2, @rn := 1, @rn := 1)
  ) AS rn
 FROM your_table YT
 CROSS JOIN 
 (
  SELECT @prevStatus := -1, @rn := 1
 ) AS var 
 ORDER BY YT.date
) AS t
WHERE t.rn = 1
ORDER BY t.ID

from this post

edited Sep 10 '21 at 09:49

answered Sep 10 '21 at 09:36

hongnhat

157
2
2
12

This is my solution to this question. I also edit and add links to the original solution, thanks – hongnhat Sep 10 '21 at 09:51

MySQL: Extract the date when a value in a column has changed

4 Answers4