How to get just changed rows in mysql?-CodePudding

I have a table that has followed rows:

ID  price   rowNo
1   100     1
1   100     2
1   200     3
1   100     4
1   300     5
1   100     6
1   100     7
2   500     9
2   500     10
2   500     11
2   500     12
2   500     13
2   500     14
3   400     15

I want to get rows for each ID that the price has been changed. the output will be as follow:

   ID   price   rowNo
    1   100     1
    1   200     3
    1   100     4
    1   300     5
    1   100     6
    2   500     9
    3   400     15

CodePudding user response：

You could use correlated sub queries in the where clause to test previous value or for existence

drop table if exists t;
create table t
(ID int, price int,   rowNo int);
insert into t values
(1 ,  100  ,   1),
(1 ,  100  ,   2),
(1 ,  200  ,   3),
(1 ,  100  ,   4),
(1 ,  300  ,   5),
(1 ,  100  ,   6),
(1 ,  100  ,   7),
(2 ,  500  ,   9),
(2 ,  500  ,   10),
(2 ,  500  ,   11),
(2 ,  500  ,   12),
(2 ,  500  ,   13),
(2 ,  500  ,   14),
(3 ,  400  ,   15);

select t.*
from t
where t.price <> (select t1.price from t t1 where t1.id = t.id and t1.rowno < t.rowno order by t1.rowno desc limit 1) or
        (select t1.price from t t1 where t1.id = t.id and t1.rowno < t.rowno order by t1.rowno desc limit 1) is null;

 ------ ------- ------- 
| ID   | price | rowNo |
 ------ ------- ------- 
|    1 |   100 |     1 |
|    1 |   200 |     3 |
|    1 |   100 |     4 |
|    1 |   300 |     5 |
|    1 |   100 |     6 |
|    2 |   500 |     9 |
|    3 |   400 |    15 |
 ------ ------- ------- 
7 rows in set (0.003 sec)

CodePudding user response：

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

For older MySQL version that does not support window function:

SELECT  id,price,rowNo
FROM  ( SELECT *, 
              IF(@prevprice = YT.price, @rn := @rn   1,
              IF(@prevprice := YT.price, @rn := 1, @rn := 1)
      ) AS rn
FROM test_tbl YT
CROSS JOIN 
 (
  SELECT @prevprice := -1, @rn := 1
 ) AS var 
 ORDER BY YT.id
) AS t
WHERE t.rn = 1
ORDER BY t.id

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a0deed41b868781e7b7a84b69556769e

Result:

id    price   rowNo
1 100      1
1 200      3
1 100      4
1 300      5
1 100      6
2 500      9
3 400      15