# Determine Current price of each product

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;
``````