Query in SQL SERVER2014 for a report (I need the last ROW of a table) I'm using SQL Server 2014 and I have a problem with a query.

[MY TABLES]1

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
As with most things SQL-related, there is more than one answer in this case. Here is one solution that might work for you. It relies on a lesser known function called "row_number" (Microsoft documentation here) which, in this case, can help you avoid making multiple passes through temp tables (though that would also work for your needs)

;WITH deliveries_report AS (
    SELECT
        id_order ,
        id_Delivery ,
        itemcode ,
        qty,
        ROW_NUMBER()  OVER (PARTITION BY itemcode ORDER BY id_Delivery DESC) AS row_num
    FROM
        #deliveries
    WHERE
        id_order=9
)

SELECT 
    id_order ,
    id_Delivery ,
    itemcode ,
    qty
FROM 
    deliveries_report 
WHERE 
    row_num=1 
ORDER BY 
    id_Delivery DESC    


Just replace #deliveries with the name of your deliveries-table. Good luck!