SQL Case Order 按特定顺序

Question

好的，我之前也问过类似的问题，但是我已经研究过了，并没有具体找到这个。我有一个表，需要根据字段 OptionName(NVarChar) 和 IsActive(BIT) 进行排序。

我需要 DDL 的结果按以下顺序排列:

Option A
Option B
Option C
Options that are Active, by OptionName ASC 
Option D
Options that are Inactive, by OptionName ASC 

目前为止

ORDER BY CASE WHEN PortalName = 'Company, Inc' THEN 0 ELSE 1 END,
         CASE WHEN PortalName = 'Setup' THEN 1 ELSE 2 END,
         CASE WHEN PortalName = 'Daily Routine' THEN 2 ELSE 3 END,
         CASE WHEN IsActive = 1 THEN 3 ELSE 4 END, 
         CASE WHEN PortalName = 'Master Option' THEN 4 ELSE 5 END,
         PortalName ASC

但这会返回结果:

Option A
Option B
Option C
Option D
Options that are Active, by OptionName ASC 
Options that are Inactive, by OptionName ASC 

任何帮助都会很棒!

Answer 1

尝试，

ORDER BY CASE WHEN PortalName = 'Company, Inc' THEN 0 
              WHEN PortalName = 'Setup' THEN 1 
              WHEN PortalName = 'Daily Routine' THEN 2 
              WHEN IsActive = 1 THEN 3 
              WHEN PortalName = 'Master Option' THEN 4 
         ELSE 5 END,
         PortalName ASC