Selecting latest rows in subgroups

Question

I have the following table created by a join and some conditionals:

product_id     date
11111          2012-06-05
11111          2012-05-01
22222          2011-05-01
22222          2011-07-02
33333          2011-01-01

I am trying to get the rows such that I have a result set with the latest date per product:

GOAL

product_id     date
11111          2012-06-05
22222          2011-07-02
33333          2011-01-01  

I could extract the data as is and do a manual sort, but I'd rather not. I cannot seem to find a way to do a SELECT MAX() without returning only a single row, and I'd rather not run a query for each product id.

The table is generated by this query:

SELECT item_id, sales_price, item, description, transaction_date 
FROM db.invoice_line AS t1 INNER JOIN db.invoices AS t2 
ON t1.invoice_id = t2.id_invoices WHERE item IS NOT NULL 
AND item_id != '800001E9-1325703142' AND item_id != '800002C3-1326830147' 
AND invoice_id IN 
    (SELECT id_invoices FROM db.invoices 
       WHERE customer_id = '[variable customer id]' 
       AND transaction_date >= DATE_SUB(NOW(), INTERVAL 360 DAY));

I use a join to 'add' the date column. After that, I disregard useless items, and select from invoices from a particular customer from a year ago to date.

Thanks for any guidance.

Dane

Answer 1

Looks like a group by would fit the bill:

select  product_id
,       max(date)
from    YourTable
group by
        product_id