How to get just changed rows in mysql?

Question

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

Welcome to Stack Overflow! Visit the [help], take the [tour] to see what and [ask]. Please first ***>>>[Search for related topics on SO](https://www.google.com/search?q=mysql+return+unique+field+values+site%3Astackoverflow.com)<<<*** and if you get stuck, post a [mcve] of your attempt, noting input and expected output using the [`[<>]`](https://meta.stackoverflow.com/questions/358992/ive-been-told-to-create-a-runnable-example-with-stack-snippets-how-do-i-do) snippet editor. — mplungjan, Dec 16 '21 at 09:01
I think you might want to look for duplicates, this should push you in the right direction: https://stackoverflow.com/questions/3935078/mysql-count-duplicates — Martijn, Dec 16 '21 at 09:05

score 1 · Accepted Answer · answered Dec 16 '21 at 10:09

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)

score 0 · Answer 2 · answered Dec 16 '21 at 10:02

**

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

How to get just changed rows in mysql?

2 Answers2