Difference between two rows in MySQL Workbench, but LAG is not authorized

Question

I have a dataset as in this example :

id | product_id  |        date       |  weight
1  |    454      |2019-06-26 16:08:45|   900
2  |    454      |2019-06-27 13:24:16|   900
3  |    454      |2019-06-28 10:53:42|   899
4  |    352      |2018-04-18 10:53:42|   124
5  |    352      |2018-04-19 15:26:51|   124
6  |    112      |2019-12-08 11:44:01|   065
7  |    375      |2020-03-15 08:23:43|   483
8  |    375      |2020-03-15 18:07:33|   496
9  |    375      |2020-03-16 14:32:24|   496

And I would like to get only the rows that have a weight different from the previous one or different from the next one. In the case of the example the expected output is :

id | product_id  |        date       |  weight
2  |    454      |2019-06-27 13:24:16|   900
3  |    454      |2019-06-28 10:53:42|   899
7  |    375      |2020-03-15 08:23:43|   483
8  |    375      |2020-03-15 18:07:33|   496

However, I have only reading permissions on this database, so the LAG() function does not work. What other options do I have?

Thank you!

Answer 1

One method uses correlated subqueries:

select t.*
from (select t.*,
             (select t2.weight
              from t t2
              where t2.product_id = t.product_id and t2.date < t.date
              order by t2.date desc
              limit 1
             ) as prev_weight,
             (select t2.weight
              from t t2
              where t2.product_id = t.product_id and t2.date > t.date
              order by t2.date asc
              limit 1
             ) as next_weight
      from t
     ) t
where prev_weight <> weight or next_weight <> weight;