Same table, maximum group date by type, 2 row difference

Question

How can i do this? I have this table

╔════════╦══════════╦══════════╗
║ item   ║ price    ║ date     ║
╠════════╬══════════╬══════════╣
║ Dollar ║       60 ║ 1.3.2016 ║
║ Dollar ║       50 ║ 2.3.2016 ║
║ Bound  ║      100 ║ 1.3.2016 ║
║ Bound  ║      110 ║ 2.3.2016 ║
║ Euro   ║      600 ║ 1.3.2016 ║
║ Euro   ║      580 ║ 3.3.2016 ║
╚════════╩══════════╩══════════╝

The output should display one element from each type with the last default of the price on the last date and the price difference from the previous line, for example:

╔════════╦══════════╦════════════════════════╗
║ item   ║ price    ║ date     ║ differnece  ║
╠════════╬══════════╬════════════════════════╣
║ Dollar ║       50 ║ 2.3.2016 ║   -10       ║
║ Bound  ║      110 ║ 2.3.2016 ║    10       ║
║ Euro   ║      580 ║ 3.3.2016 ║   -20       ║
╚════════╩══════════╩════════════════════════╝

I did it ...

---

SELECT item,date,price FROM wpdatatable_23 WHERE date IN (SELECT max(date) FROM wpdatatable_23 GROUP BY item LIMIT 0,1) group by item

... and it works fine. I just don’t know how to make a difference, any help?

Answer 1

You can calculate the difference using the correlated subquery:

SELECT item, `date`, price,
       price - (SELECT price
                FROM wpdatatable_23 AS t2
                WHERE t2.item = t1.item AND t2.`date` < t1.date 
                ORDER BY t2.`date` DESC LIMIT 0,1) AS difference
FROM wpdatatable_23 AS t1 
WHERE date IN (SELECT max(date) 
               FROM wpdatatable_23 
               GROUP BY item ) 

Answer 2

Check this out (a small modification of Giorgos SQL):

SELECT
    a.item,
    a.`date`,
    a.price,
    a.price  - (SELECT price
                FROM prices AS t2
                WHERE t2.item = a.item AND t2.`date` < a.`date` 
                ORDER BY t2.`date` DESC LIMIT 0,1) as diff
FROM
    prices AS a
WHERE
    a.date = (SELECT MAX(date) FROM prices b WHERE b.item = a.item GROUP BY item);