CODEWITHSUNDEEP
mysqlsql
llanato
llanato

Reputation: 2491

MySQL: Get product with most recent price for each store

I have the two tables below, there can be many price entries against one product,I'm looking to output the product name with the most recent product information for each store. I'm using the below but not getting the expected result, there can be hundreds products with hundreds of price records each with hundreds of stores I've only two here as an example.

product

product_id | name      | cat_id
---------------------------------
1          | product 1 | 124567
2          | product 2 | 834392
3          | product 3 | 234723

price

product_id  | price | store | added
--------------------------------------------------
1           | 1.00  | 1     | 2016-01-01 00:00:00
1           | 1.50  | 2     | 2016-01-01 00:00:00
2           | 3.75  | 1     | 2016-01-01 00:00:00
2           | 4.00  | 2     | 2016-01-01 00:10:00
2           | 3.99  | 2     | 2016-01-01 00:00:00
3           | 34.56 | 1     | 2016-01-01 00:00:00

Expected Output

name        | price | store | added
--------------------------------------------------
product 1   | 1.00  | 1     | 2016-01-01 00:00:00
product 1   | 1.50  | 2     | 2016-01-01 00:00:00
product 2   | 3.75  | 1     | 2016-01-01 00:00:00
product 2   | 4.00  | 2     | 2016-01-01 00:10:00
proudct 3   | 34.56 | 1     | 2016-01-01 00:00:00

SQL

SELECT `prod`.`name`
, `prc`.`price`
, `prc`.`store`
, `prc`.`added`
FROM `product` `prod`
LEFT JOIN `price` `prc` ON `prod`.`product_id` = `prc`.`product_id`
ORDER BY `prod`.`cat_id` DESC
, `prc`.`added` DESC
, `prc`.`price` DESC;

Upvotes: 0

Views: 329

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You can use NOT EXISTS to eliminate price rows where there exists a newer price for the same store/product;

SELECT name, price, store, added
FROM product
JOIN price 
  ON product.product_id = price.product_id
WHERE NOT EXISTS (
  SELECT 1 FROM price p 
  WHERE price.product_id = p.product_id
    AND price.store = p.store
    AND price.added < p.added
);

An SQLfiddle to test with.

Upvotes: 3

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

your query should updated to be:

SELECT `prod`.`name`
, max(`prc`.`price`)
, `prc`.`store`
, `prc`.`added`
FROM `product` `prod`
LEFT JOIN `price` `prc` ON `prod`.`product_id` = `prc`.`product_id`
group by `prod`.`name`, `prc`.`store`
ORDER BY `prod`.`cat_id` DESC
, `prc`.`added` DESC
, `prc`.`price` DESC;

Upvotes: 0

Related Questions