Hello,
I have a table of data. I want to return distinct order id's and the highest item id associated with the order. With the example table attached, I would want to return 3 records with item ids 7202, 7205, 7208. I then want to use the results to join to another table. Basically, I want the highest item id associated with the order id.
Example Table
Auto Id
Order Id
Item Id
1
100
7200
2
100
7201
3
100
7202
4
101
7203
5
101
7204
6
101
7205
7
102
7206
8
102
7207
9
102
7208
Result I want
Auto Id
Order Id
Item Id
3
100
7202
6
101
7205
9
102
7208
And then with the results
Select Auto Id, Order Id, Item Id
FROM Table A
INNER JOIN Table B
ON A.Auto ID = B. Auto Id
/* Setup Test Data Table */
DECLARE @MyTable TABLE
(
AutoId INT NOT NULL PRIMARY KEY,
OrderId INT NOT NULL,
ItemId INT NOT NULL,
UNIQUE (OrderId, ItemId)
);
/* Insert into Test Data Table */
INSERT INTO @MyTable (AutoId, OrderId, ItemId)
SELECT *
FROM
(
VALUES
(1, 100, 7200),
(2, 100, 7201),
(3, 100, 7202),
(4, 101, 7203),
(5, 101, 7204),
(6, 101, 7205),
(7, 102, 7206),
(8, 102, 7207),
(9, 102, 7208)
) AS MyList (AutoId, OrderId, ItemId);
/* Generate RN to remove duplicates */
;WITH cteNoDuplicates As
(
SELECT mt.AutoId, OrderId, ItemId, ROW_NUMBER() OVER(PARTITION BY OrderId ORDER BY ItemId DESC) As RN
FROM @MyTable As mt
)
SELECT d.AutoId, d.OrderId, d.ItemId
FROM cteNoDuplicates As d
WHERE d.RN = 1;
Thanks James. Although this removes the duplicates it is not returning the 3 records I want. This will return item id 7200, 7203 and 7206. It is giving me the first result in the partition when what I need is the last result in the partition.
Can you tweak to get the desired result?
;WITH abc_cte
AS (SELECT orderid,
Max(itemid) AS max
FROM @MyTable
GROUP BY orderid)
SELECT b.*
FROM abc_cte
JOIN @MyTable b
ON abc_cte.orderid = b.orderid
AND abc_cte.max = b.itemid
When I run my code and your code together your code is 18% of the batch cost while mine is 82%. Granted that is with few rows but it seems to me your code is far more efficient.
My code requires additional operators to run. Those are: Sort (very expensive), Sequence Project, and Filter.