Show rows where column value changed
SQL is set-oriented, but it can solve row-by-row problems. Suppose you need to retrieve only the rows that differ from immediately previous rows given some ordering spec:
drop table if exists t;
create table t (
p char(3),
d date
);
insert into t values
('50%','2024-05-01'),
('30%','2024-05-02'),
('30%','2024-05-03'),
('50%','2024-05-04'),
('50%','2024-05-05'),
('20%','2024-05-06'),
('20%','2024-05-07'),
('50%','2024-05-08'),
('70%','2024-05-09'),
('70%','2024-05-10');
select * from t order by d;
+------+------------+
| p | d |
+------+------------+
| 50% | 2008-05-01 | *
| 30% | 2008-05-02 | *
| 30% | 2008-05-03 |
| 50% | 2008-05-04 | *
| 50% | 2008-05-05 |
| 20% | 2008-05-06 | *
| 20% | 2008-05-07 |
| 50% | 2008-05-08 | *
| 70% | 2008-05-09 | *
| 70% | 2008-05-10 |
+------+------------+
We want to retrieve only rows whose `p` values differ from immediately previous values (marked by * above). As with running sums. we get the desired listing by tracking row-to-row value changes with user variables:
set @p='';
set @d='';
select p 'Pct Changed',d Date from (
select
p,
if( p<>@p, d, @d ) as d,
@p:=p,
@d:=d
from t
order by d
) as t
group by d;
+-------------+------------+
| Pct Changed | Date |
+-------------+------------+
| 50% | 2008-05-01 |
| 30% | 2008-05-02 |
| 50% | 2008-05-04 |
| 20% | 2008-05-06 |
| 50% | 2008-05-08 |
| 70% | 2008-05-09 |
+-------------+------------+