Mysql: Select new price for products based on date

Question

We have a script that runs once per day to update prices for an associated system by looking up new product prices by date. Ex:

ID | product_id | date       | price
------------------------------------
1  | 1          | 2014-03-01 | 199

2  | 1          | 2014-04-01 | 299

3  | 2          | 2014-03-11 | 150

4  | 2          | 2014-04-17 | 172

So, our script runs on April 1, and performs a SELECT * FROM table WHERE date='2014-04-01'; and then uses that product_id column to update the other system with the new price. This all works perfectly fine. We've got a problem now though where if the system doesn't run one day, or the user puts in a date in the past, the system will never update with the new pricing.

So the question is: What would the query look like that selects the max(date) for each distinct product_id where date <= today?

I've tried various methods of distinct() and group by's, but can't ever get the result I'm looking for. That result would be that when the script runs on April 2, it sees that the max date < today is April 01, so grabs those prices.

Can't you just use `SELECT * FROM table WHERE date >= NOW()`? I don't see why you would need to specify the date when MySQL has built in datetime functions that can determine that for you — Jacob, Mar 11 '14 at 20:18
Well that is functionally incorrect. We need where date <= NOW() (Because the price changed a week ago) But we only need to select ONE price for each product. That query is going to return the March, Feb, Jan, Dec 2013, etc... prices for every product, when we only need March's. — Kevin, Mar 11 '14 at 20:24
`SELECT product_id, MAX(DATE) FROM tbl WHERE date <= NOW() GROUP BY product_id` — AgRizzo, Mar 11 '14 at 20:25
My bad with the wrong operator, I always get those mixed up. AgRizzo is on the right track. I would also do what Dion said and order by date, limit by 1 — Jacob, Mar 11 '14 at 20:26
@AgRizzo - That is one of the queries we tried, but it doesn't work exactly right. One of our products is 2460 | 743 | 2014-03-01 | 3984.75 AND 2459 | 743 | 2014-04-01 | 4233.80 but when we run the query (Replacing NOW() with '2014-04-25') we are given product 743 with '2014-04-01' as the date but '3984.75' as the price - That price belongs to the '2014-03-01' record. — Kevin, Mar 11 '14 at 20:31
It sounds like you potentially used the MySQL GROUP BY flavor, not the ANSI standard GROUP BY - See @fthiella solution for the full implementation — AgRizzo, Mar 11 '14 at 20:37

score 2 · Accepted Answer · answered Mar 11 '14 at 20:25

You probably are looking for this:

SELECT *
FROM table
WHERE date=(SELECT MAX(date) FROM table WHERE date<='2014-04-01')

or if products can have different dates:

SELECT t1.*
FROM table t1 INNER JOIN (SELECT product_id, MAX(date) max_date
                          FROM table
                          WHERE date<='2014-04-01'
                          GROUP BY product_id) t2
     ON t1.product_id=t2.product_id AND t1.date=t2.max_date

score 1 · Answer 2 · edited May 23 '17 at 12:21

1

(Maybe SELECT * FROM table WHERE date<'2014-04-01' ORDER by date DESC LIMIT 1 The date of course has to be replaced but this would select the last entry before this date.)

Edit: maybe this could help: Oracle equivalent of Postgres' DISTINCT ON?

edited May 23 '17 at 12:21

Community

1
1

answered Mar 11 '14 at 20:20

Dion

3,145
20
37

That will only return a single price. We have thousands of products. We need to select the new prices for every single one of them, not just one. – Kevin Mar 11 '14 at 20:25

Mysql: Select new price for products based on date

2 Answers2