I've searched here and found several examples that are similar to my situation. The difference I believe is that in the examples all the data is in one table. Using the Northwind database Here's my sql:
select p.ProductID, o.orderID, o.OrderDate
from Products p
join [Order Details] od on p.ProductID = od.ProductID
join Orders o on o.OrderID = od.OrderID
join (select o1.OrderID, max(o1.OrderDate) as MaxOrder
from orders o1 group by o1.Orderid) as o2
on o.orderid = o2.orderid
and o.orderdate = MaxOrder
where CategoryID = 1
I'm getting 404 rows, I'm expecting 12.
Can anyone tell me what I'm doing wrong?