MySQL Select one row with a unique attribute value

Question

I create a stock storage system and decided to store each product balance (every time it was updated) in the following table:

+------------+--------------+---------+------+ | Product_id | Warehouse_id | Balance | Date | +------------+--------------+---------+------+ 

Example:

Staff adds 10 pieces to product_id 123 in store_id 5

 +------------+--------------+---------+-------------+ | Product_id | Warehouse_id | Balance | Date | +------------+--------------+---------+-------------+ | 123 | 5 | 10 | 2013-09-16 | +------------+--------------+---------+-------------+ 

The character then adds 3 pieces to product 234 in store_id 5 and 5 pieces to 123 in store_id 5,

 +------------+--------------+---------+-------------+ | Product_id | Warehouse_id | Balance | Date | +------------+--------------+---------+-------------+ | 123 | 5 | 10 | 2013-09-16 | | 234 | 5 | 3 | 2013-09-18 | | 123 | 5 | 15 | 2013-09-21 | +------------+--------------+---------+-------------+ 

* Note the date column

Now let me add some more lines

 +------------+--------------+---------+-------------+ | Product_id | Warehouse_id | Balance | Date | +------------+--------------+---------+-------------+ | 123 | 5 | 10 | 2013-09-16 | | 234 | 5 | 3 | 2013-09-18 | | 123 | 5 | 15 | 2013-09-21 | | 123 | 5 | 18 | 2013-09-24 | | 234 | 5 | 10 | 2013-09-26 | | 123 | 5 | 22 | 2013-09-29 | +------------+--------------+---------+-------------+ 

---

How can I launch a request that will allow me to get the balance of all products as of September 25, 2013?

This means that I need the following result:

 +------------+--------------+---------+-------------+ | Product_id | Warehouse_id | Balance | Date | +------------+--------------+---------+-------------+ | 234 | 5 | 3 | 2013-09-18 | | 123 | 5 | 18 | 2013-09-24 | +------------+--------------+---------+-------------+ 

In short, I need the last line (by date) for product_id.

Any help would be greatly appreciated!

Answer 1

Assuming product balances are maintained in one warehouse, you can do this as follows

 SELECT t.product_id, t.warehouse_id, t.balance, t.date FROM table1 t JOIN ( SELECT warehouse_id, product_id, MAX(date) date FROM table1 WHERE date <= '2013-09-25' GROUP BY warehouse_id, product_id ) q ON t.warehouse_id = q.warehouse_id AND t.product_id = q.product_id AND t.date = q.date 

Output:

  |  PRODUCT_ID |  WAREHOUSE_ID |  BALANCE |  DATE |
 | ------------ | -------------- | --------- | ----------- - |
 |  234 |  5 |  3 |  2013-09-18 |
 |  123 |  5 |  18 |  2013-09-24 |

Here is the SQLFiddle demo

Answer 2

 SELECT * FROM TABLE WHERE (PRODUCT_ID, DATE) IN (SELECT PRODUCT_ID, MAX(DATE) FROM TABLE WHERE DATE <= '2013-09-25' GROUP BY PRODUCT_ID ) 

Answer 3

Query:

SQLFIDDLEExample

  SELECT * FROM table1 t WHERE t.`Date` = (SELECT MAX(t2.`Date`) FROM Table1 t2 WHERE t2.`Date` <= '2013-09-25' AND t2.product_id = t.product_id)