MySQL Group By not producing expected result

Question

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

Please be more clear about your expected result...show it as a table result — Shankar R10N, Oct 23 '09 at 07:45
want recent records group by product_id, so that i get only one record of product. — Satish Ravipati, Oct 23 '09 at 08:09
In the above 6 record, for product_id '1', the recent record is rec_id 5 and for product_id '2', the recent record is rec_id 6 — Satish Ravipati, Oct 23 '09 at 08:12
http://stackoverflow.com/questions/1313120/sql-retrieving-the-last-record-in-each-group — ArK, Oct 23 '09 at 08:21

score 0 · Answer 1 · answered Oct 23 '09 at 11:32

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
)

score 0 · Answer 2 · answered Nov 18 '09 at 20:27

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.

MySQL Group By not producing expected result

2 Answers2