I have a table to manage the stock which looks like below
product batch qty date
Grape BAT0 42 10/3/2016
Apple BAT1 12 11/3/2016
Banana BAT2 15 11/3/2016
Orange BAT3 24 11/3/2016
Pine BAT4 28 11/3/2016
Apple BAT1 9 12/3/2016
Banana BAT2 10 12/3/2016
Pine BAT4 25 12/3/2016
Apple BAT1 5 13/3/2016
Banana BAT2 6 13/03/2016
GreenApple BAT5 15 13/03/2016
If I pass the date 12/03/2016
then I need the last dated row to return as below
product batch qty date
Grape BAT0 42 10/3/2016
Orange BAT3 24 11/3/2016
Apple BAT1 9 12/3/2016
Banana BAT2 10 12/3/2016
Pine BAT4 25 12/3/2016
Also I have more than 160,000 rows in that table and it will grow each day.
How can I write an optimised query?
Best Answer
It's a bit hard to understand the logic of the query from the explanation, but I think you mean you get a date as input and you need the closest by date for each product?
If so, I have a solution, pretty sure not the optimal one, but maybe the logic can lead you in the right direction: