Determine Current price of each product

Question

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.

The sample data you've provided cannot produce the output that you claim it does, because those values do not exist in the data. There is no `18.30` or `2019-10-12` in the sample data. You should fix that as well, and show the actual output you're getting. — Ken White, Nov 18 '21 at 03:18
Ah ok! It was because the actual data has many many lines of code, I will change that to show it though — puyopop, Nov 18 '21 at 03:21
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Nov 18 '21 at 07:57
I suggest you think very differently about this goal. Typically an active OLTP system needs to know the "current" price very often. Usually all the old prices have no use beyond infrequent managerial or historic reporting. This might be a situation where intentional duplication can be beneficial - storing the current prices separately from your current table. — SMor, Nov 18 '21 at 12:47

score 1 · Accepted Answer · answered Nov 18 '21 at 03:22

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;

Determine Current price of each product

1 Answers1