user2244110 user2244110 - 3 years ago 187
MySQL Question

How can I get the latest item price in mysql query?

I manage to get the lowest, highest and average price of the item but couldn't get the latest price. Below is the select query i am using by joining the item and item_price tables. How can I fix the problem?

$sql = 'SELECT *, MIN(ip_price) AS lowest_price, MAX(ip_price) AS highest_price,
AVG(ip_price) AS average_price, MAX(ip_price_date) AS latest_date,
(SELECT ip_price FROM cnf_item_price WHERE ip_price_date = "latest_date") AS latest_price
FROM cnf_item
INNER JOIN cnf_item_price ON cnf_item_price.ip_item_id = cnf_item.it_id
WHERE 1 AND cnf_item_price.ip_supp_id=?
GROUP BY cnf_item.it_id
ORDER BY cnf_item.it_name ASC';
$stmt = $DB->prepare($sql);
$stmt->bindValue(1,$supplier_id);
$stmt->execute();

Answer Source

can you try following query:

$sql = 'SELECT *, MIN(ip_price) AS lowest_price, MAX(ip_price) AS highest_price, 
        AVG(ip_price) AS average_price, MAX(ip_price_date) AS latest_date,
        (SELECT ip_price FROM cnf_item_price order by ip_price_date desc limit 1) AS latest_price
        FROM cnf_item
        INNER JOIN cnf_item_price ON cnf_item_price.ip_item_id = cnf_item.it_id
        WHERE 1 AND cnf_item_price.ip_supp_id=?
        GROUP BY cnf_item.it_id
        ORDER BY cnf_item.it_name ASC';
$stmt = $DB->prepare($sql);
$stmt->bindValue(1,$supplier_id);
$stmt->execute();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download