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');