Question 20640 in Mysql Question posted in
The official MySQL documentation can be found here.

Find a value in the certain date based on update record column – Mysql

Table looks like this:

id  Product     Price   price_update_date
1   Product     21.99   2022-11-09 6:32:11
2   Product     22.99   2022-09-27 9:06:14
3   Product     19.99   2022-07-10 6:27:49
4   Product     18.99   2022-05-24 7:31:20
5   Product     17.98   2022-04-21 8:59:18
6   Product     17.99   2022-02-02 8:33:48
7   Product     19.99   2021-12-20 8:43:41
8   Product     18.99   2021-11-29 2:31:00
9   Product     19.99   2021-10-11 7:42:17
10  Product     19.98   2021-06-10 5:11:03
11  Product     19.99   2021-02-25 2:23:45

Here i have only price update records. I need to find what price was on a specific date. Lets say, 2022-10-01 (October 1st). Any ideas how to perform it in current situation?

Mysql version 5.6.51

Tags:

2

Answers


  1. 0

    To find the price of a product on a specific date when you only have the price update records, you can use a SQL query to find the most recent update record for each product that is on or before the specific date, and then extract the corresponding price value. Here is an example query that can achieve this:

    SELECT Product, Price
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY price_update_date DESC) AS rn
    FROM product_prices
    WHERE price_update_date <= '2022-10-01'
) AS t
WHERE rn = 1;

    This query first filters the records to only include those that are on or before the specified date (‘2022-10-01’ in this case). It then uses the ROW_NUMBER() window function to assign a rank to each record within its product group, with the most recent record receiving a rank of 1. Finally, it selects only those records with a rank of 1, which will be the most recent record for each product on or before the specified date.

    Note that this query assumes that each product has at least one price update record, and that there are no gaps or overlapping records in the dataset. If there are any missing or incomplete records, the results of this query may be incorrect. Additionally, the query assumes that the price_update_date column is stored as a datetime data type. If it is stored as a string, you may need to use a different format specifier in the WHERE clause to compare dates properly.

    Login or Signup to reply.
  2. 0

    To retrieve for a single product it would be:

    SELECT *
FROM product_prices
WHERE price_update_date <= '2022-10-01'
AND Product  = 'Product'
ORDER BY price_update_date DESC
LIMIT 1;

    Or for all products:

    SELECT pp.*
FROM product_prices pp
JOIN (
    SELECT Product, MAX(price_update_date) max_date
    FROM product_prices
    WHERE price_update_date <= '2022-10-01'
    GROUP BY Product
) t ON pp.Product = t.Product AND pp.price_update_date = t.max_date;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search