how about:
select productname, categoryid, max(od.orderid) as lastOrderID, max(orderdate) as lastOrderDate
from products p
left join [order details] od on od.productid = p.productid
left join orders o on od.orderid = o.orderid
where categoryid = 1
group by productname, categoryid
Chai |
1998-05-05 00:00:00.000
Chang
1998-05-06 00:00:00.000
Chartreuse verte
1998-05-06 00:00:00.000
Côte de Blaye
1998-04-17 00:00:00.000
Guaraná Fantástica
1998-05-05 00:00:00.000
Ipoh Coffee
1998-05-04 00:00:00.000
Lakkalikööri
1998-05-06 00:00:00.000
Laughing Lumberjack Lager
1998-04-28 00:00:00.000
Outback Lager
1998-04-30 00:00:00.000
Rhönbräu Klosterbier
1998-05-06 00:00:00.000
Sasquatch Ale
1998-05-01 00:00:00.000
Steeleye Stout
1998-04-23 00:00:00.000