Determine Current price of each product

November 18, 2021

I am trying to figure out how to determine the latest price of a product. I am struggling because the price is not updating, and the date is not reaching the right numbers.
Here is the code I have attempted to write (I know it is not the best):

SELECT p.prodID, pr.price, MAX(pr.[from]) [date] 
    FROM Price pr
    INNER JOIN Product p ON p.prodId = pr.prodId
    GROUP BY p.prodID, pr.price;

With this problem, I have a database that shows the price for multiple different days, but I am trying to find it for the most recent, for example of the data:

INSERT INTO Price VALUES
(36, '20191221', 16.47)
INSERT INTO Price VALUES
(36, '20191226', 16.47)
INSERT INTO Price VALUES
(36, '20191229', 12.81)
INSERT INTO Price VALUES
(36, '20191230', 12.81)

This is the table for price as well:

Create Table Price (
        prodId int Foreign Key References Product(prodId),
        [from] date,
        price decimal(10,2),
        Constraint PK_Price Primary Key (prodId, [from])
    );

For simplicity, I am just focusing on product 36, but there are many different products. The current results show the price as 18.30 and the date as 2019-10-12 I want the results to show the price as 12.81 and the date as 2019-12-30.

>Solution :

You can use the row_number() window function to assign a number for the prices ordered by the date for each product.

SELECT x.[prodid],
       x.[from],
       x.[price]
       FROM (SELECT p.[prodid],
                    p.[from],
                    p.[price],
                    row_number() OVER (PARTITION BY p.[prodid]
                                       ORDER BY p.[from] DESC) [rn]
                    FROM price p) x
       WHERE x.[rn] = 1;