Better way to find the latest change in sql value

Question

I have 2 tables, one of which lists items, the other of which keeps track of when items changed in price.

 price_table:
 price_id int
 item_id int
 price_amount float
 change_date date // this date is stored as Y-m-d, without a time

and

 item_table:
 item_id int
 item_name char(40)
 item_description char(40)

I know that the following finds the item_ids changed at the current date:

 SELECT item_id
 FROM price_table
 WHERE change_date = "$today"

when

 $today = date('Y-m-d');

I then run another query for EACH item_id returned (this is why I think my method is inefficient)

SELECT card_price
FROM price_table
WHERE item_id
ORDER BY change_date ASC

And get the last 2 values from the PHP array, comparing them to get the difference in price. My test had an inner join to return the item_name as well, but I've removed that to simplify the question. Is there a better way to do this? And if so, can it be expanded to use the last 2 or 3 days as the change criteria instead?

possible duplicate of [mysql: Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group) — Barmar, Sep 22 '13 at 04:19

paul1923 · Accepted Answer · 2013-09-22T07:01:49.597

This is what triggers are for, I'm also going to talk about DATE_SUB and date intervals

Using triggers will allow you to keep the current price in the price table and keep historical prices in a separate table.

For instance, consider creating a new table called price_table_old

price_table_old:
price_id int  
item_id int
price_amount float
change_date date

On the price_table, create an 'update' trigger...

insert into price_table_old ( `item_id`,  `price_amount` ,  `change_date ` )  
VALUES  ( OLD.`item_id` , OLD.`price_amount` , NOW() )

To help you with trigger syntax, I copied the trigger I used for a staff table below

When ever someone changes a price, the price_table_old table is automatically updated.

From here, things are a little more logical to get the current price and comparisons of previous prices.

You could run a query like....

SELECT 
    price_table.item_id , 
    price_table.price_amount , 
    (price_table.price_amount - price_table_old.price_amount ) AS pricediff , 
    price_table_old.change_date 
FROM 
    price_table
LEFT JOIN 
    price_table_old ON price_table_old.item_id = price_table.item_id 
ORDER BY price_table.item_id , price_table_old.change_date DESC

or stick a where in there to zone in on a specific item and/or date range and/or a limit to get the last (say) 3 price changes.

For instance, to get a list of price changes for the last 3 days on all items, you could use a statement like this

SELECT 
    price_table.item_id , 
    price_table.price_amount , 
    (price_table.price_amount - price_table_old.price_amount ) AS pricediff , 
    price_table_old.change_date 
FROM 
    price_table
LEFT JOIN 
    price_table_old ON price_table_old.item_id = price_table.item_id 
WHERE 
    price_table_old.change_date > ( DATE_SUB( NOW() , INTERVAL 3 DAY )) 
ORDER BY price_table.item_id , price_table_old.change_date DESC

Here is my staff table trigger...

CREATE TRIGGER `Staff-CopyOnUpdate` BEFORE UPDATE ON `staff` 
FOR EACH ROW insert into staff_old  
( `sid` , `title`,  `firstname` ,  `surname`  , `type`, 
    `email` , `notify`, `actiondate`, `action` )
VALUES  
( OLD.`sid` , OLD.`title`,  OLD.`firstname` ,  OLD.`surname`  , OLD.`type`,
    OLD.`email` , OLD.`notify`, NOW() , 'updated' )

Would the trigger still occur if I used "INSERT IGNORE" on the original table (price_table) and it failed to update due to uniqueness? — Tom, Sep 22 '13 at 08:08

Better way to find the latest change in sql value

1 Answers1