# Select rows with most recent date for the given date

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?

– cEz
Mar 20, 2016 at 9:15

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:

``````SELECT t.id, t.product
FROM table t
JOIN
(
SELECT
t.id,
t.product,
t.date, MIN(ABS(UNIX_TIMESTAMP('yourdate') - UNIX_TIMESTAMP(t.date))) AS diff
FROM table t
GROUP BY t.product
) x ON x.diff = ABS(UNIX_TIMESTAMP('yourdate') - UNIX_TIMESTAMP(t.date))
AND x.product = t.product
``````
• Pretty sure you need a product condition in the on Mar 20, 2016 at 16:21
• You are right! updated
– omri
Mar 21, 2016 at 8:40
• I need to group by product,batch and store(added field). The above query picks the first row of the subquery which is dated 2016-03-02 but my input date is 2016-03-17 Mar 25, 2016 at 11:05