CODEWITHSUNDEEP
mysqlsqlself-join
Zack Scriven
Zack Scriven

Reputation: 61

finding change between records in MySQL

I have a table where I am storing the stored number of barrels inside of many tanks. I am storing values here every night at midnight, and at the beggining and end of any operator initiated transfer.

What I want to return is the number of barrels difference since the previous event record for that specific tank. I have the correct ID for the self join to get the previous record number, however the barrels is incorrect.

Here is what I currently have. 

SELECT
    inventory.id,
    MAX(inventory2.id) AS id2,
    inventory.tankname,
    inventory.barrels,
    inventory.eventstamp,
    inventory2.barrels
FROM 
    inventory
LEFT JOIN
    inventory inventory2 ON inventory2.tankname = inventory.tankname AND inventory2.eventstamp < inventory.eventstamp
GROUP BY
    inventory.id,
    inventory.tankname,
    inventory.barrels,
    inventory.eventstamp
ORDER BY
    inventory.tankname,
    inventory.eventstamp

That returns the following

enter image description here

Upvotes: 1

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Just use correlated subqueries:

SELECT i.*,
       (SELECT i2.id
        FROM inventory i2
        WHERE i2.tankname = i.tankname AND
              i2.eventstamp < i.eventstamp
        ORDER BY i2.eventstamp DESC
        LIMIT 1
       ) as prev_id,
       (SELECT i2.barrels
        FROM inventory i2
        WHERE i2.tankname = i.tankname AND
              i2.eventstamp < i.eventstamp
        ORDER BY i2.eventstamp DESC
        LIMIT 1
       ) as prev_barrels
FROM inventory i
ORDER BY i.tankname, i.eventstamp;

Your query doesn't work because you have columns in the SELECT that are not in the GROUP BY and are not aggregated. That shouldn't be allowed in any database; it is unfortunate that MySQL does allow it.

Upvotes: 1

Related Questions