MySQL: Query not always returning lowest most recent price across multiple stores

Question

The below SQL is returning the lowest most recent price for each product if there is only price information for a single store, if there is price information for more then one store the most recent lowest price isn't always returned!?

Wanted Outcome

Return the lowest price for a product from the most recent price information across all stores so if there were 6 stores it would display the lowest price from the most recent price record for each of the 6 stores.

If the most recent price is the same across all store price records and the updated date is also the same then the records can be ordered by store to select the price for the product in question.

{product id} is a value passed in depending what product is being displayed.

SQL

SELECT `vsp`.`prod_id`
, `vsp`.`price`
, `vsp`.`store`
, `vsp`.`updated`
FROM `price` `vsp`
WHERE NOT EXISTS
(
    SELECT * 
    FROM `price` `vsp2`
    WHERE `vsp2`.`prod_id` = `vsp`.`prod_id`
    AND `vsp`.`prod_id` = {product id}
    AND (`vsp2`.`updated` > `vsp`.`updated` OR (`vsp2`.`updated` = `vsp`.`updated` AND `vsp2`.`price` < `vsp`.`price`))
) AND `vsp`.`prod_id` = {product id}

price

prod_id     | price     | store | updated
--------------------------------------------------------
product 1   | 1.99      | 1     | 2016-01-20 00:00:00
product 2   | 1.49      | 1     | 2016-01-20 00:10:00
product 2   | 1.19      | 2     | 2016-01-20 00:00:00
product 3   | 12.49     | 1     | 2016-01-20 00:00:00
product 3   | 12.49     | 2     | 2016-01-20 00:00:00
product 4   | 9.89      | 1     | 2016-01-20 00:00:00
product 5   | 10.00     | 1     | 2016-01-20 00:10:00
product 5   | 9.99      | 2     | 2016-01-20 00:00:00
product 5   | 10.49     | 3     | 2016-01-20 00:00:00

Expected Output

product 1   | 1.99      | 1     | 2016-01-20 00:00:00
product 2   | 1.49      | 1     | 2016-01-20 00:10:00
product 3   | 12.49     | 1     | 2016-01-20 00:00:00
product 4   | 9.89      | 1     | 2016-01-20 00:00:00
product 5   | 9.99      | 2     | 2016-01-20 00:00:00

Current Output

product 1   | 1.99      | 1     | 2016-01-20 00:00:00
product 2   | 1.49      | 1     | 2016-01-20 00:10:00
product 3   | 12.49     | 1     | 2016-01-20 00:00:00
product 4   | 9.89      | 1     | 2016-01-20 00:00:00
product 5   | 10.00     | 1     | 2016-01-20 00:10:00

Update #1

The select itself looks to be working great and only returning one price when I add the prodId constraint onto the query, the issue now is if I add the select statement to a function it gives the below error even though I only see one value returned when I run the select on it's own.

Result consisted of more than one row

SQL Function

CREATE FUNCTION FN_GET_SET_LOWEST_PRICE (`prodId` VARCHAR(20))
RETURNS DOUBLE
BEGIN
    DECLARE `latestPrice` DOUBLE(7,2) DEFAULT 0;

    SELECT p3.price
    INTO `latestPrice`
    FROM price p3
    INNER JOIN
    (
        SELECT p1.prod_id, MIN(p1.price) AS minPrice
        FROM price p1
        INNER JOIN
        (
            SELECT prod_id, MIN(updated) AS minUpdated
            FROM price
            GROUP BY prod_id
        ) p2
        ON p1.prod_id = p2.prod_id AND p1.updated = p2.minUpdated
        GROUP BY p1.prod_id
    ) t
    ON p3.prod_id = t.prod_id AND p3.price = t.minPrice
    WHERE p3.prod_id = prodId;

    RETURN `latestPrice`;
END//
DELIMITER ;

Answer 1

I would gravitate towards using a series of inner joins to get your result set, rather than a complicated subquery in the WHERE clause, which is hard to read.

SELECT p3.prod_id, p3.price, p3.store, p3.updated
FROM price p3
INNER JOIN
(
    SELECT p1.prod_id, MIN(p1.price) AS minPrice
    FROM price p1
    INNER JOIN
    (
        SELECT prod_id, MIN(updated) AS minUpdated
        FROM price
        GROUP BY prod_id
    ) p2
    ON p1.prod_id = p2.prod_id AND p1.updated = p2.minUpdated
    GROUP BY p1.prod_id
) t
ON p3.prod_id = t.prod_id AND p3.price = t.minPrice

Follow the link below for a running demo:

SQLFiddle

Update:

If you want to use this query like a function, then you will have to create a stored procedure. The reason for this is that a function must return only one value. Even if you contrive a query which should only return a single value, MySQL will still complain.

Try something along the following:

CREATE PROCEDURE FN_GET_SET_LOWEST_PRICE(prodId VARCHAR(20))
BEGIN
    SELECT p3.price
    FROM price p3
    INNER JOIN
    (
        SELECT p1.prod_id, MIN(p1.price) AS minPrice
        FROM price p1
        INNER JOIN
        (
            SELECT prod_id, MIN(updated) AS minUpdated
            FROM price
            GROUP BY prod_id
        ) p2
        ON p1.prod_id = p2.prod_id AND p1.updated = p2.minUpdated
        GROUP BY p1.prod_id
    ) t
    ON p3.prod_id = t.prod_id AND p3.price = t.minPrice
    WHERE p3.prod_id = prodId;
END

Answer 2

I guess this is what you actually want:

1 - find the last updated
2 - find the cheapest if last updated is multiple

And here is the SQL:

SELECT price.*
FROM price, (
    SELECT price.prod_id, last_price.max_updated, min(price.price) min_price
    FROM price, (
        SELECT prod_id,max(updated) max_updated 
        FROM price
        GROUP BY 1
    ) as last_price
    WHERE price.prod_id = last_price.prod_id
    AND   price.updated = last_price.max_updated
    GROUP BY 1,2
) as lowest_price
WHERE price.prod_id = lowest_price.prod_id
AND   price.updated = lowest_price.max_updated
AND   price.price   = lowest_price.min_price