MySQL Filter result again

Question

The goal here is to: 1. Fetch the row with the most recent date from EACH store for EACH ingredient. 2. From this result, compare the prices to find the cheapest store for EACH ingredient.

I can accomplish either the first or second goal in separate queries, but not in the same. How can i filter out a selection and then apply another filter on the previous result?

EDIT: I've been having problems with results that i get from MAX and MIN since it just fetches the rest of the data arbitrarily. To avoid this im supposed to join tables on multiple columns (i guess). Im not sure how this will work with duplicate dates etc.

I've included an image of a query and its output data.

If we use ingredient1 as an example, it exists in three separate stores (in one store twice on different dates).

In this case the cheapest current price for ingredient1 would be store3. If the fourth row dated 2013-05-25 was even cheaper, it would still not "win" due to it being out of date. (Disregard brandname, they dont really matter in this problem.)

Would appreciate any help/input you can offer!

Answer 1

This question is really interesting!

So, first, we get the row with the most recent date from EACH store for EACH ingredient. (It is possible that the most recent dates from each store can be different.) Then, we compare the prices from each store (regardless of the date) to find the least price for each ingredient.

The query below uses the GROUP_CONCAT function in good measure. Here's a SO question regarding the use of the function.

SELECT
   i.name as ingredient_name
  , MIN(store_price.price) as price
  , SUBSTRING_INDEX(
    GROUP_CONCAT(store_price.date ORDER BY store_price.price),
    ',',
    1
    ) as date
  , SUBSTRING_INDEX(
    GROUP_CONCAT(s.name ORDER BY store_price.price),
    ',',
    1
    ) as store_name
  , SUBSTRING_INDEX(
    GROUP_CONCAT(b.name ORDER BY store_price.price),
    ',',
    1
    ) as brand_name
FROM
  ingredient i
JOIN
(SELECT
  ip.ingredient_id as ingredient_id
  , stip.store_id as store_id
  , btip.brand_id as brand_id
  , CONVERT(SUBSTRING_INDEX(
    GROUP_CONCAT(ip.ingredient_price_id ORDER BY ip.date DESC),
    ',',
    1
    ), UNSIGNED INTEGER) as ingredient_price_id
  , MAX(ip.date) as date
  , CONVERT(SUBSTRING_INDEX(
    GROUP_CONCAT(ip.price ORDER BY ip.date DESC),
    ',',
    1
    ), DECIMAL(5,2)) as price
FROM ingredient_price ip
JOIN store_to_ingredient_price stip ON ip.ingredient_price_id = stip.ingredient_price_id
JOIN brand_to_ingredient_price btip ON ip.ingredient_price_id = btip.ingredient_price_id
GROUP BY 
  ip.ingredient_id
  , stip.store_id) store_price
ON i.ingredient_id = store_price.ingredient_id
JOIN store s ON s.store_id = store_price.store_id
JOIN brand b ON b.brand_id = store_price.brand_id
GROUP BY
  store_price.ingredient_id;

You can check the implementation on this SQL Fiddle.

The version below, which ignores the brand, is slightly smaller:

SELECT
   i.name as ingredient_name
  , MIN(store_price.price) as price
  , SUBSTRING_INDEX(
    GROUP_CONCAT(store_price.date ORDER BY store_price.price),
    ',',
    1
    ) as date
  , SUBSTRING_INDEX(
    GROUP_CONCAT(s.name ORDER BY store_price.price),
    ',',
    1
    ) as store_name
FROM
  ingredient i
JOIN
(SELECT
  ip.ingredient_id as ingredient_id
  , stip.store_id as store_id
  , CONVERT(SUBSTRING_INDEX(
    GROUP_CONCAT(ip.ingredient_price_id ORDER BY ip.date DESC),
    ',',
    1
    ), UNSIGNED INTEGER) as ingredient_price_id
  , MAX(ip.date) as date
  , CONVERT(SUBSTRING_INDEX(
    GROUP_CONCAT(ip.price ORDER BY ip.date DESC),
    ',',
    1
    ), DECIMAL(5,2)) as price
FROM ingredient_price ip
JOIN store_to_ingredient_price stip ON ip.ingredient_price_id = stip.ingredient_price_id
GROUP BY 
  ip.ingredient_id
  , stip.store_id) store_price
ON i.ingredient_id = store_price.ingredient_id
JOIN store s ON s.store_id = store_price.store_id
GROUP BY
  store_price.ingredient_id;

References: Simulating First/Last aggregate functions in MySQL