I'm using SQL Server 2014 and I have a problem with a query.
I want to have in my report, ALL the items of the order with
ID_Order = 9 that have been delivered. And for the items that have been delivered at two times (Item Code = Art3 for example), I just want to have the last row, that means the last delivery of this Item, with NO repetition.
I already tried these two queries without success:
Attempt #1: DISTINCT
SELECT DISTINCT Order.ItemCode, Delivery. Qty, Delivery.ID_Delivery, Order.ID_Order FROM Delivery INNER JOIN Order ON Order.ID_Order = Delivery.ID_Order WHERE Order.ID_Order = '9'
Attempt #2: subquery
SELECT * FROM (SELECT Order.ItemCode, Delivery.Qty, FROM Delivery INNER JOIN Order ON Order.ID_Order = Delivery.ID_Order WHERE Order.ID_Order = '9') GROUP BY a.ItemCode, a.Qty