user3240704 user3240704 - 2 months ago 8
SQL Question

Dense_Rank ordering

I've read a few variations of this question, but the solutions don't seem to be working.

I wish to dynamically create a "Subgroup" for each "OrderNo" & "GroupID". Subgroups should be ordered by "OrderLine" eg: (Expected outcome)

OrderNo OrderLine GroupID Subgroup
------------------------------------
10463 1 798 1
10463 2 799 2
10463 3 797 3
10463 5 65 4
10463 6 65 4
10463 7 65 4
10481 4 917 1
10481 5 918 2
10481 6 131 3
10481 7 131 3
10481 8 131 3
10481 9 130 4


I've used Dense_Rank() to create the correct groups below but the ordering(and rank) is totally incorrect.

SELECT
OrderNo, OrderLine, GroupID,
DENSE_RANK() OVER (PARTITION BY OrderNo ORDER BY GroupID) AS Subgroup
FROM
#temptable
ORDER BY
OrderNo, OrderLine;


Output:

OrderNo OrderLine GroupID Subgroup
------------------------------------
10463 1 798 3
10463 2 799 4
10463 3 797 2
10463 5 65 1
10463 6 65 1
10463 7 65 1
10481 4 917 3
10481 5 918 4
10481 6 131 2
10481 7 131 2
10481 8 131 2
10481 9 130 1


Query:

-- Temp tables
CREATE TABLE #temptable
(
OrderNo varchar(5),
OrderLine int,
GroupID int
);

INSERT INTO #temptable (OrderNo, OrderLine, GroupID)
VALUES ('10463', '1', '798'), ('10463', '2', '799'),
('10463', '3', '797'), ('10463', '5', '65'),
('10463', '6', '65'), ('10463', '7', '65'),
('10481', '4', '917'), ('10481', '5', '918'),
('10481', '6', '131'), ('10481', '7', '131'),
('10481', '8', '131'), ('10481', '9', '130');

Answer

You want to order the DENSE_RANK partition by the OrderLine, but in cases where more than one record has the same GroupID, you want the rank to be the same. One option is to use a subquery to identify assign a single value for the OrderLine (say the minimum) for a set of records which have the same GroupID. This table can then be joined back to your #temptable, and the effective OrderLine can be used to DENSE_RANK as you want.

SELECT t1.OrderNo,
       t1.OrderLine,
       t1.GroupID,
       DENSE_RANK() OVER (PARTITION BY t1.OrderNo ORDER BY t2.OrderLine) AS Subgroup
FROM #temptable t1
INNER JOIN
(
    SELECT OrderNo,
           MIN(OrderLine) AS OrderLine,
           GroupID
    FROM #temptable
    GROUP BY OrderNo,
             GroupID
) t2
    ON t1.OrderNo = t2.OrderNo AND
       t1.GroupID = t2.GroupID
Comments