Answered by:
COUNT DISTINCT WITHIN WINDOW

-
Hi All,
I am trying to get a distinct count within a window. For example:
use tempdb; IF OBJECT_ID('T1', 'U') IS NOT NULL DROP TABLE T1; CREATE TABLE T1 ( ShipperID int primary key, OrderID int, PickID int ); INSERT INTO T1 VALUES (47243, 703, 10653), (67268, 703, 40673), (6729, 703, 40697) /* ShipperID OrderID PickID 47243 703 10653 67268 703 40673 67269 703 40697 */ INSERT INTO T1 VALUES (67274, 30743, 40709), (67275, 30743, 40709), (67276, 30743, 40709) /* ShipperID OrderID PickID 67274 30743 40709 67275 30743 40709 67276 30743 40709 */ SELECT ShipperID, OrderID, PickID, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY PickID) AS BackOrderCount, ROW_NUMBER() OVER (PARTITION BY OrderID, PickID ORDER BY ShipperID) AS SplitShipCount FROM T1
I want my BackOrderCount for OrderID 30743 rows to be 1 i.e. I only want my BackOrderCount to increment for each unique PickID within an OrderID.
Any assistance much appreciated.
Thanks.
Clay
Question
Answers
-
Hi Clay,
The simple answer is DENSE_RANK function.
SELECT ShipperID,
OrderID,
PickID,
DENSE_RANK() OVER (PARTITION BY OrderID ORDER BY OrderID,PickID) AS BackOrderCount,
ROW_NUMBER() OVER (PARTITION BY OrderID, PickID ORDER BY ShipperID) AS SplitShipCount
FROM
T1To know detail about Dense Rank refer http://technet.microsoft.com/en-us/library/ms173825.aspx
Regards, RSingh
- Proposed as answer by Dhamodharan_M Tuesday, November 19, 2013 7:48 AM
- Marked as answer by clay123123123 Tuesday, November 19, 2013 10:35 PM
All replies
-
Try below
;with cte as ( SELECT ShipperID,OrderID, PickID, ROW_NUMBER() OVER (PARTITION BY ShipperID ORDER BY OrderID) AS BackOrderCount, ROW_NUMBER() OVER (PARTITION BY OrderId,PickID ORDER BY ShipperID) AS SplitShipCount FROM T1 ) select * from cte where BackOrderCount = 1 and SplitShipCount =1
if this output is not correct then please provide your desired output results.ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
-
Thanks for the reply Eshani - perhaps I didn't fully explain myself.
The BackOrderCount should reset for each new OrderID and increment for each PickID within an OrderID
In my example query - I am trying to use row_number() which increments for each new row - a new row is created for a new PickID or ShipperID.
SELECT ShipperID, OrderID, PickID, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY PickID) AS BackOrderCount, ROW_NUMBER() OVER (PARTITION BY OrderID, PickID ORDER BY ShipperID) AS SplitShipCount FROM T1 ShipperID OrderID PickID BackOrderCount SplitShipCount 47243 703 10653 1 1 67268 703 40673 2 1 6729 703 40697 3 1 67274 30743 40709 1 1 67275 30743 40709 2 2 67276 30743 40709 3 3
Each PickID against an OrderID is considered a 'BackOrder'.
Each ShipperID against an OrderID, PickID combo is considered a 'SplitShip'.
Thanks for your help.
- Edited by clay123123123 Tuesday, November 19, 2013 1:17 AM
-
-
-
I've got a solution - not sure it's a great one!!!
;WITH OrdersNPicks AS ( SELECT DISTINCT OrderID, PickID FROM T1), BackOrderCounts AS ( SELECT OrderID, PickID, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY PickID) AS BackOrderNumber FROM OrdersNPicks) SELECT t.ShipperID, t.OrderID, t.PickID, c.BackOrderNumber, ROW_NUMBER() OVER (PARTITION BY t.OrderID, t.PickID ORDER BY t.ShipperID) AS SplitShipCount FROM T1 t JOIN BackOrderCounts c ON c.OrderID = t.OrderID AND c.PickID = t.PickID
In case there is a better way -- I will leave this open for a short while... -
Hi Clay,
The simple answer is DENSE_RANK function.
SELECT ShipperID,
OrderID,
PickID,
DENSE_RANK() OVER (PARTITION BY OrderID ORDER BY OrderID,PickID) AS BackOrderCount,
ROW_NUMBER() OVER (PARTITION BY OrderID, PickID ORDER BY ShipperID) AS SplitShipCount
FROM
T1To know detail about Dense Rank refer http://technet.microsoft.com/en-us/library/ms173825.aspx
Regards, RSingh
- Proposed as answer by Dhamodharan_M Tuesday, November 19, 2013 7:48 AM
- Marked as answer by clay123123123 Tuesday, November 19, 2013 10:35 PM
-