how to create an inventory summary report from mysql database

I'm trying to create an inventory summary report for my inventory database and I'm getting confused as to how to do it. This is how my table looks like:

Stock_ID | Date | PO_ID | DR_ID | Product_ID | Stock_In | Stock_Out | Stock_Balance |

I want my output to look like this:

Product_ID Stock_Balance

1                    9000

2                    8000

3                    9000

4                    10000

5                    5000

6  开发者_Python百科                  9000

7                    9000

8                    9000

I have used this sql script to generate the above output:

select Product_ID, Stock_Balance from stock_card

where Date = ( select max(Date) from stock_card )

group by Product_ID

Order by Product_ID, Stock_Balance

Unfortunately this script has a problem that when a product id's max date is lower than the others then it does not show up in the results.

Please help I'm new at using SQL and it's really confusing me.

---

Perform the grouping in a subquery, then find the matching row.

select Product_ID, Stock_Balance
FROM stock_card s JOIN (
    select MAX(Stock_ID) MaxID
    FROM stock_card
    GROUP BY Product_ID
  ) g ON s.Stock_ID = g.MaxID
Order by Product_ID, Stock_Balance