One to many join with group by

Question

I have two tables. one table is named Shopper and it looks like

SHOPPER_ID | SHOPPER_NAME | 
-------------------------
1          | Marianna     | 
2          | Jason        | 

and another table named Order has information like Date on the order

ORDER_ID | SHOPPER_ID | DATE
----------------------------------
1        | 1          | 08/09/2012
2        | 1          | 08/08/2012

Now I want to do a query that joins two tables and group by SHOPPER_ID, because one shopper can have multiple orders, I want to pick the latest order base on DATE value.

My query looks like:

Select * from Shopper as s join Order as o 
on s.SHOPPER_ID = o.SHOPPER_ID 
group by s.SHOPPER_ID

The query is wrong right now because I don't know how to apply the filter to only get the latest order. Thanks in advance!

Answer 1

I suggest using a sub-select:

Select s.SHOPPER_ID, s.SHOPPER_NAME, o.MAX_DATE
  from Shopper s
  INNER join (SELECT SHOPPER_ID, MAX(DATE) AS MAX_DATE
                FROM ORDER
                GROUP BY SHOPPER_ID) o 
    on s.SHOPPER_ID = o.SHOPPER_ID

Best of luck.