Ordering by 2 columns to get a distinct

I am trying to compare dimensions from 2 different tables-box and container. I join on the order Id and the width, height and weight. I need both Primary Ids to be in the final query, but cannot join on them, since they are not related. I want to get the distinct dimensions for each ID, but when I add both IDs in, the records double because of the ordering.

 CREATE TABLE #box
(OrderId int
,BoxId int
,width int
,height int
,weight int)

INSERT INTO #box(OrderId,BoxId, width, height, weight)
VALUES
(655543, 54386, 30 , 12 , 6 ),
(655543, 54386, 30 , 12 , 6 ),
(655543, 27421, 30 , 12 , 6 ),
(655543, 27421, 30 , 12 , 6 )

CREATE TABLE #container
(OrderId int
,ContainerId int
,width int
,height int
,weight int)

INSERT INTO #container(OrderId,ContainerId, width, height, weight)
VALUES
(655543, 12353, 30 , 12 , 6 ),
(655543, 12353, 30 , 12 , 6 ),
(655543, 65437, 30 , 12 , 6 ),
(655543, 65437, 30 , 12 , 6 )

SELECT DISTINCT b.OrderId,c.ContainerId,b.BoxId,b.width,b.height,b.weight
FROM #box b
JOIN #container c ON c.OrderId =b.OrderId
AND c.width=b.width AND c.height=b.height
AND c.weight=b.weight
ORDER BY c.ContainerId,b.BoxId

If both box Id and Container Id would be ordered at the same time, there would be 2 distinct records, and not 4.

How can I have ContainerId and BoxID ordered in a way that the results of this query returns 2 records (with all columns included)?

I can do two sub queries, and have a ROW_NUMBER, but I was wondering if there is a straight way to do this.

from Recent Questions - Stack Overflow https://ift.tt/3daTZN4
https://ift.tt/eA8V8J