CODEWITHSUNDEEP
mysql
llanato
llanato

Reputation: 2491

MySQL: Most recent lowest price per product across multiple stores

I'm trying to get a list of the most recent updated products ordered descending by updated field of which has the lowest price as well but only ever one record per product, the issue is that the most recent record for each store is valid so I need to pull out the lowest price based on the most recent prices across all stores for a product, so it will just return the most recent lowest price.

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

The flow is that if there is only one record for prod_id then that's used, if there are two or more entries for a prod_id then orders by the updated column in descending order so the lowest price across the most recent record across the stores for that prod_id is displayed.

SQL

SELECT `price`
, `store`
, `updated`
FROM `price` 
ORDER BY `updated` DESC
, `store` ASC
LIMIT 10;

The main issue is I can't figure out how to make it only pull out the one record for a product with the lowest price amongst the most recent records across the stores for the product, should I be using a function to do the logic outlined above or is there a way to apply the above logic to a normal SELECT query?

Upvotes: 0

Views: 212

Answers (1)

SIDU
SIDU

Reputation: 2278

Always remember this kind of SQL only works in sub-query: 

SELECT distinct price.*
FROM price, (
    SELECT prod_id, max(updated) last_updated
    FROM price
    group by 1
) as price_last
WHERE price.prod_id = price_last.prod_id
AND price.updated = price_last.last_updated

If you are sure updated,prod_id is unique (which means each second any product only updated once), you can remove the "distinct"

Upvotes: 0

Related Questions