MySQL Group By not producing expected result

This is my table structure:

rec_id  product_id  quantity  quantity_in  quantity_out  balance  stock_date  status

   1      2          342          NULL           17        325    2009-10-23     1
   2      2          325          NULL          124        201    2009-10-23     1
   3      1          156          NULL           45        111    2009-10-23     1
   4      2          201          NULL          200          1    2009-10-23     1
   5      2            1          NULL            1          0    2009-10-23     1
   6      1          111          NULL           35         76    2009-10-23     1

All I want is the last transaction done for a given product: product_id, quantity, quantity_out and balance from this table.

Example, there are 2 transaction done for product 2 (ids 1 & 2):

final balance for product_id 2 is 0 -> stored in rec_id 5

final balance开发者_运维问答 for product_id 1 is 76 -> stored in rec_id 6

Final result/output should be like this:

recid  productid  quantity  quantityin  quantityout  balance  stock_date  status
  5         2         1       NULL            1         0     2009-10-23    1
  6         1       111       NULL           35        76     2009-10-23    1

You can find the latest record for each product like:

select max(rec_id) as MaxRec
from YourTable
group by product_id

Using a subquery, you can retrieve the latest rows for their product:

select *
from YourTable
where rec_id in (
    select max(rec_id) as MaxRec
    from YourTable
    group by product_id
)

Here's a single query with no subqueries:

SELECT main.*
FROM YourTable main
LEFT JOIN YourTable newer
    ON newer.product_id = main.product_id AND newer.rec_id > main.rec_id
WHERE newer.rec_id IS NULL;

You can tweak the field list however you want--make sure you select fields from main, not newer, which should be all null.