Compare table to itself and get the changed records

Question

I have a set of data in prices table:

id date          price
1  01/01/2001    100
2  01/02/2001    100
3  01/03/2001    200
4  01/04/2001    200
5  01/05/2001    300
6  01/06/2001    300
7  01/07/2001    100
9  01/08/2001    100
10 01/09/2001    200
20 01/10/2001    100

I want to get only record whose price changes:

id date          price
1  01/01/2001    100
3  01/03/2001    200
5  01/05/2001    300
7  01/07/2001    100
10 01/07/2001    200
20 01/10/2001    100

How can I do it in SQL, one query only. Thanks!

The MySQL script:

CREATE TABLE prices (
  id int(11) NOT NULL AUTO_INCREMENT,
  date date NOT NULL,
  price int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

insert  into prices(id,date,price) 
values (1,'2001-01-01',100),
(2,'2001-01-02',100),(
3,'2001-01-03',200),
(4,'2001-01-04',200),
(5,'2001-01-05',300),
(6,'2001-01-06',300),
(7,'2001-01-07',100),
(9,'2001-01-08',100),
(10,'2001-01-09',200),
(20,'2001-01-10',100);

Answer 1

Try this:

SELECT * FROM prices 
WHERE id in (
    SELECT p.id from prices p 
    LEFT JOIN prices p1 
    ON (p1.id = (select max(id) from prices pp where pp.id < p.id)) 
    WHERE p.price != p1.price OR p1.price is null
);

Answer 2

you will be requried to use left join on self table.

SELECT * FROM prices 
WHERE id in (
    SELECT p2.id from prices p2 
    LEFT JOIN prices p3 
    ON (p3.id = (select max(id) from prices p4 where p4.id < p2.id)) 
    WHERE p2.price != p3.price OR p2.price is null
);

try sqlfiddle