SQL Server Order By - tricky

Question

SortOrder     FundType
9              DD
2              DD
13             FF
22             BB
11             FF

I want to group by FundType and then Order By SortOrder. But I want to maintain this order w/ respect to FundType: DD, FF, BB

So I want the final output to look like:

SortOrder  FundType
2           DD
9           DD
11          FF
13          FF
22          BB

I cannot to an Order By FundType DESC, SortOrder because then FF will appear first and I cannot do an Order By FundType, SortOrder because then BB will appear first.

How can I set up a query where I specify the order of FundType, and then apply an Order By within each FundType grouping? Thanks.

Answer 1

You can use a CTE to calculate the order of each group:

;WITH CTE AS (
   SELECT FundType, MIN(SortOrder) AS GroupOrder
   FROM #mytable
   GROUP BY FundType
)
SELECT *
FROM CTE

The above, given the following input:

CREATE TABLE #mytable (SortOrder INT, FundType VARCHAR(10))
INSERT INTO #mytable VALUES
(9,  'DD'),
(2,  'DD'),
(4,  'FF'),
(22, 'BB'),
(11, 'FF')

produces this output:

FundType    GroupOrder
----------------------
BB          22
DD          2
FF          4

We can use now this CTE to sort by group order, then by the SortOrder:

;WITH CTE AS (
  ... cte statements here
)
SELECT m.*
FROM CTE AS c
INNER JOIN #mytable AS m ON c.FundType = m.FundType
ORDER BY GroupOrder, SortOrder

Output:

SortOrder   FundType
--------------------
2           DD
9           DD
4           FF
11          FF
22          BB