1

I have a table called daily_stock with the following fields:

+-----------+----------+------------+----------+
| warehouse | product  | date       | stock    |
+-----------+----------+------------+----------+
| 01        | A        | 2016/04/01 | 100      |
| 01        | A        | 2016/04/05 | 80       |
| 01        | A        | 2016/04/18 | 35       |
| 02        | A        | 2016/04/02 | 1        |
| 02        | A        | 2016/04/06 | 10       |
| 02        | A        | 2016/04/16 | 18       |
| 01        | B        | 2016/04/03 | 25       |
| 01        | B        | 2016/04/11 | 66       |
| 01        | B        | 2016/04/29 | 45       |
| 02        | B        | 2016/04/05 | 19       |
| 02        | B        | 2016/04/19 | 1        |
+-----------+----------+------------+----------+

Now I need to display products last date with stock in all warehouses.

The result I am trying to get is:

+----------+----------+------------+----------+
| 01       | A        | 2016/04/18 | 35       |
| 02       | A        | 2016/04/16 | 18       |
| 01       | B        | 2016/04/29 | 45       |
| 02       | B        | 2016/04/19 | 1        |
+----------+----------+------------+----------+
  • Please add what RDBMS you are using. That will help get you an answer. – tpet May 9 '16 at 17:06
  • I am using MariaDB 10.0 with HeidiSQL if that is what you are asking. – HEki May 9 '16 at 17:14
2

Does this work for you? I am testing in MySQL.

SELECT LatestStock.*
FROM daily_stock as LatestStock
JOIN (  
    SELECT warehouse, product, MAX([date]) as MAXdate
    FROM daily_stock 
    GROUP BY warehouse, product
    ) as LatestDate on LatestStock.warehouse = LatestDate.warehouse AND LatestStock.product = LatestDate.product AND LatestStock.date = LatestDate.MAXdate
ORDER BY LatestStock.product, LatestStock.warehouse
  • I get 0 results with this query :( Thank you for your reply. – HEki May 9 '16 at 18:17
  • Do you get results if you just run the inner query to find the latest date for warehouse and product? – tpet May 9 '16 at 18:19
  • Yes, i get results if i only run the inner query. I think i can work with that! Thank you! – HEki May 9 '16 at 18:25
  • How about if i have more months in the date field? How would i go and show stock on last day in month for all the months in data table? – HEki May 10 '16 at 5:29
  • @HEki: That looks like a different question, even if similar. – Andriy M May 10 '16 at 9:48

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.