1

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

migrated from serverfault.com Jun 30 '17 at 19:54

This question came from our site for system and network administrators.

1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy