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

Question

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();

1. Don't use evil `SELECT *`. Fix that. Then get back to us. — Strawberry, Sep 30 '17 at 10:27
To get the last update return you can use a PHP function affected row look at https://stackoverflow.com/questions/7368225/how-do-i-tell-when-a-mysql-update-was-successful-versus-actually-updated-data — , Sep 30 '17 at 10:28
Oh, and see [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Sep 30 '17 at 10:29

score 0 · Accepted Answer · answered Sep 30 '17 at 10:25

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();

The subselect should be correlated. And you shouldn't use `SELECT *` in a statement with GROUP BY. — Paul Spiegel, Sep 30 '17 at 17:12

user2244110 · Answer 2 · 2017-10-01T03:13:06.060

Thanks #ahmet kamaran. It's working for my case here. I also replace * with those columns needed as suggested by others. So, after some testing from the above suggestions, here is my codes which retrieve those data i needed.

$sql = 'SELECT it_id, it_code, it_name, it_desc, 
        ip_id, ip_item_id, ip_supp_id, ip_price, ip_price_date, ip_ref_no, ip_remarks, 
        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();

http://sqlfiddle.com/#!9/ca6234/2

Thanks.

score 0 · Answer 3 · edited Jul 27 '20 at 19:03

SELECT it_id, it_code, it_name, it_desc, 
            ip_id, ip_item_id, ip_supp_id, ip_price, ip_price_date, ip_ref_no, ip_remarks, 
            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 cnf_item_price.ip_item_id = cnf_item.it_id
            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 
            GROUP BY cnf_item.it_id
            ORDER BY cnf_item.it_name ASC;

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

3 Answers3