CODEWITHSUNDEEP
sql-server-2008t-sql
thatuxguy
thatuxguy

Reputation: 2528

SQL query to get latest prices, depending on the date

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.

Upvotes: 0

Views: 3547

Answers (2)

Zeph
Zeph

Reputation: 1728

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

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

The easiest would be to use a CTE with ROW_NUMBER function:

WITH CTE AS
(
  SELECT dbo.twProducts.title, dbo.LowestPrices.productAsin, dbo.twProducts.sku, 
       dbo.LowestPrices.tweAmzPrice, dbo.LowestPrices.price, dbo.LowestPrices.priceDate,
       RN = ROW_NUMBER()OVER( PARTITION BY productAsin ORDER BY priceDate DESC)
  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
)
SELECT * FROM CTE WHERE RN = 1

Upvotes: 1

Related Questions