Reputation: 405
I have the following problem. Having four columns, Product_ID, Product_price, Start_date and End_date I need to select a product with the highest price on a given day(1997/12/14). The point is that product id can remain the same in few rows but the product has different prices in different time periods. I've tried to do something like this but I am getting an empty result:
SELECT
Product_ID, Product_price
FROM Products
WHERE
Start_date >= STR_TO_DATE('1997/12/14', '%y/%m/%d')
AND
End_date =< STR_TO_DATE('1997/12/14', '%y/%m/%d')
Upvotes: 1
Views: 75
Reputation: 235
Try to implement this, will give you an expected result.
select p.ID,p.Price
from product p
where '<Your Date>'<=p.EndDate AND '<Your Date>'>=p.StartDate And p.Price=(select
max(p.Price) from product p);
Upvotes: 0
Reputation: 133400
you could use a join on max price
select Product_ID, Product_price
from Products
inner join (
select max(Product_price) max_price
from Products
WHERE STR_TO_DATE('1997/12/14', '%y/%m/%d') between Start_date AND End_date
) t on Products.Product_price = t.max_price
Upvotes: 0
Reputation: 1271151
I think the logic you want is more like this:
SELECT p.Product_ID, p.Product_price
FROM Products p
WHERE p.Start_date <= '1997-12-14' AND
p.End_date >= '1997-12-14'
ORDER BY p.Product_price DESC
LIMIT 1;
In other words, your date comparisons are backwards. A time period contains a particular date if it starts on or before that date and ends on or after the date.
Upvotes: 1