0

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?

|improve this question
  • Adding the field definitions would be helpful – Cez Mar 20 '16 at 9:15
1

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
|improve this answer
  • Pretty sure you need a product condition in the on – paparazzo Mar 20 '16 at 16:21
  • You are right! updated – omri Mar 21 '16 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 – dharanbro Mar 25 '16 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.