question

solo0802 avatar image
solo0802 asked

Query in SQL SERVER2014 for a report (I need the last ROW of a table)

I’m using SQL Server 2014 and i’m having a problem with a Query, you can see my problem in the picture : ![alt text][1] [1]: /storage/temp/4132-ade17ae6d93c15ad630ec23a65a1816f8fec52b3.png favorite I’m using SQL Server 2014 and i’m having a problem with a Query, you can see my problem in the picture : My tables I want to have in my report, ALL the items of the ordre « ID_Order = 9 » that have been delivered. And for the items that have been Delivered in two times (Item Code = Art3 for exemple) **I just want to have the last row, that meens the last delivery of this Item, with NO repetition.** I already tried this two queries without success : 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' 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 Thank you in advance !
sqlquerysql-server-2014
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Here's an example, that shows how to use ROW_NUMBER() to identify multiple-part deliveries and only take the last one declare @delivery table ( ID_Order int, ID_Delivery int, ItemCode varchar(10), Qty int); insert into @delivery select 9,23,'Ar1',1; insert into @delivery select 9,23,'Ar3',2; insert into @delivery select 9,24,'Ar2',3; insert into @delivery select 9,24,'Ar3',2; with cte_AllDeliveries as ( select ID_Order, ID_Delivery, ItemCode, Qty, row_number()over(partition by ItemCode order by ID_Delivery desc) as rn from @delivery ) select cte_AllDeliveries.ID_Order, cte_AllDeliveries.ID_Delivery, cte_AllDeliveries.ItemCode, cte_AllDeliveries.Qty from cte_AllDeliveries where cte_AllDeliveries.rn = 1 order by cte_AllDeliveries.ID_Delivery desc; ID_Order ID_Delivery ItemCode Qty ----------- ----------- ---------- ----------- 9 24 Ar2 3 9 24 Ar3 2 9 23 Ar1 1 (3 row(s) affected)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
Taking nothing aware from the Kev Riley ♦♦ solution, which is a perfect solution for the problem stated, I wonder several things concerning the problem as stated. Why is the requested solution necessary? I ask that because the actual business requirement has not been stated and the correct solution actually could be quite different. For example, it would appear that the order of deliveries is important. The question is, are you really looking for the correct order of when the delivery actually occurred? If so, then ID_Delivery isn't actually correct because ID_Delivery = 24 could actually have been completed days prior to ID_Delivery = 23 thanks to special deliveries such as "Overnight" v.s. "Standard" delivery methods. Further, ID_Delivery = 23 and 24 may have actually occurred at the same time. What is the business rule for that? You simply cannot assume that ID_Delivery = 23 will temporally come before ID_Delivery = 24. If you need to ensure the correct temporal order, then you must use a date and time using the ID_Delivery value only as a tie-breaker if such a tie must be broken. Assuming an ID contains the correct temporal order for something that may occur in a different order than they were entered into a table can cause you a real heap of trouble.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.