Need help with the MySQL comparison query

advertisements

happy new year!

I have a little problem creating a MySQL query to compare some DB entries.

There is a table named prices which looks like:

{id,timestamp,item,priceCustomer1,priceCustomer2,priceCustomer3}

sample values:

{1,1483453891,"Paper A",2.99,2.70,1.90}
{2,1483453891,"Paper B",1.99,1.70,2.90}
{3,1483453891,"Paper C",4.99,3.70,3.90}
{4,1483453704,"Paper A",2.80,2.70,1.90}
{5,1483453704,"Paper B",1.90,1.75,2.90}
{6,1483453704,"Paper C",4.99,3.70,3.90}

There are price information for different items from different moments recognizable on timestamp. The query should compare the three prices of each items from the last two timestamps and list all entries where at least one price is different.

Is my goal comprehensible?

I tried lot of statements but now I'm desperate and don't know what to try next time.

It would be very nice if somebody could help me.

Thanks so much! Greetings

edit:

Thanks for the fast replies! First query to get the last two timestamps I tried is this one:

SELECT date FROM prices GROUP BY date ORDER BY date DESC limit 2;

The others queries I tried aren't there now because of try&error at the mysql console. The result I would expect with the sample data would be like this:

{1,1483453891,"Paper A",2.99}
{4,1483453704,"Paper A",2.80}
{2,1483453891,"Paper B",1.99,1.70}
{5,1483453704,"Paper B",1.90,1.75}


Thanks for your idea xQbert... I think I have found the solution:

CREATE VIEW oldData AS
SELECT *
FROM prices
WHERE date= (SELECT date FROM prices GROUP BY date ORDER BY date DESC LIMIT 2     OFFSET 1);

CREATE VIEW newData AS
SELECT *
FROM prices
WHERE date= (SELECT date FROM prices GROUP BY date ORDER BY date DESC LIMIT 1);

SELECT *
FROM olddata JOIN newdata USING(item)
WHERE (olddata.priceCustomer1 <> newdata.priceCustomer1)
OR (olddata.priceCustomer2 <> newdata.priceCustomer2)
OR (olddata.priceCustomer3 <> newdata.priceCustomer3);

I think this produces the result, I need. Thank!