Show rows where column value changed

from the Artful Common Queries page


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%','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');
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 |
+-------------+------------+


Return to the Artful Common Queries page