SQL Case Order By specific order

问题:

Ok, I asked something similar before, but I've researched it and haven't found this specifically. I have a table that I need sorted on fields OptionName(NVarChar) and IsActive(BIT).

I need the results to be in the following order for a DDL:

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

So far I have

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

But this returns the results as:

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

Any help would be great!

回答1:

try,

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

回答2:

consider each part of the order by as a different column... Apply a case to each component. Get the first part first... then the second part. If it doesn't apply to the second part, just have it always the same value... something like...

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

回答3:

Got an answer: CASE WHEN PortalName = 'Master Option' THEN 9999 ELSE 5 END