SQL query to get the latest prices, by date

advertisements

I am trying to retrieve a list of products which have been updated, the table contains multiple updates of the products as it records the price changes.

I need to get the latest price changes for all products, but only return the the last update. I have the below code so far, but it only returns the very last update and only 1 product.

SELECT dbo.twProducts.title, dbo.LowestPrices.productAsin, dbo.twProducts.sku,
       dbo.LowestPrices.tweAmzPrice, dbo.LowestPrices.price, dbo.LowestPrices.priceDate
FROM   dbo.aboProducts INNER JOIN
       dbo.LowestPrices ON dbo.aboProducts.asin = dbo.LowestPrices.productAsin
       INNER JOIN dbo.twProducts ON dbo.aboProducts.sku = dbo.twProducts.sku
WHERE  (dbo.LowestPrices.priceDate =
        (SELECT MAX(priceDate) AS Expr1
         FROM   dbo.LowestPrices AS LowestPrices_1))

I hope this makes sense, i am not sure if i have explained it in a way thats easy to understand.

Any questions please feel free to ask.


I think the adjustment to the query you are looking for is to join your subquery rather than just matching on the Date.

SELECT dbo.twProducts.title, dbo.LowestPrices.productAsin, dbo.twProducts.sku,
       dbo.LowestPrices.tweAmzPrice, dbo.LowestPrices.price, dbo.LowestPrices.priceDate
FROM   dbo.aboProducts INNER JOIN
       dbo.LowestPrices ON dbo.aboProducts.asin = dbo.LowestPrices.productAsin
       INNER JOIN dbo.twProducts ON dbo.aboProducts.sku = dbo.twProducts.sku
WHERE  dbo.LowestPrices.priceDate IN
        (SELECT MAX(LowestPrices_1.priceDate)
         FROM   dbo.LowestPrices AS LowestPrices_1
         WHERE dbo.LowestPrices.productAsin = LowestPrices_1.productAsin)

This will match on the max(priceDate) for each product