CODEWITHSUNDEEP
mysqlsql
Patrik Votoček
Patrik Votoček

Reputation: 675

Diff value last two record by datetime

I have table with id, item_id, value (int), run (datetime) and i need select value diff betwen last two run per *item_id*.

Upvotes: 2

Views: 1141

Answers (2)

Robin Castlin
Robin Castlin

Reputation: 10996

SELECT item_id, ABS(value1 - value2) AS diff
FROM (  SELECT h.item_id, h.value AS value1, h2.value AS value2
        FROM (  SELECT id, item_id, value
                FROM table_name
                GROUP BY item_id
                ORDER BY run DESC) AS h
        INNER JOIN (    SELECT id, item_id, value
                        FROM table_name
                        ORDER BY run DESC) AS h2
        ON h.item_id = h2.item_id AND h.id != h2.id
        GROUP BY item_id) AS h3

I believe this should do the trick for you. Just replace table_name to correct name.

Explanation:
Basicly I join the table with itself in a run DESC order, JOIN them based on item_id but also on id. Then I GROUP BY them again to remove potential 3rd and so on cases. Lastly I calculate the difference between them through ABS(value1 - value2).

Upvotes: 3

Dan
Dan

Reputation: 45741

    SELECT t2.id, t2.item_id, (t2.value- t1.value) valueDiff, t2.run
    FROM ( table_name AS t1 
           INNER JOIN 
           table_name AS t2 
           ON t1.run = (SELECT MAX(run) FROM table_name where run < t2.run) 
                 and t1.item_id = t2.item_id)

This is assuming you want the diff between a record and the record with the previous run

Upvotes: 1

Related Questions