none
COUNT DISTINCT WITHIN WINDOW

    Question

  • 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


    Tuesday, November 19, 2013 12:28 AM

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
    T1

    To 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
    Tuesday, November 19, 2013 6:12 AM

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

    Tuesday, November 19, 2013 12:41 AM
  • 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.

      

    Tuesday, November 19, 2013 1:17 AM
  • BTW - my solution needs to run on SQL2008.

    Thanks.

    Tuesday, November 19, 2013 1:35 AM
  • Maybe this will help...

    Tuesday, November 19, 2013 3:23 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...
    Tuesday, November 19, 2013 3:50 AM
  • 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
    T1

    To 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
    Tuesday, November 19, 2013 6:12 AM
  • Thanks RSingh,

    Thought I was missing something simple.

    Much appreciated.

    Tuesday, November 19, 2013 10:35 PM