CODEWITHSUNDEEP
sqlsql-order-bycase
NJohns
NJohns

Reputation: 53

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!

Upvotes: 3

Views: 21029

Answers (3)

NJohns
NJohns

Reputation: 53

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

Upvotes: -1

DRapp
DRapp

Reputation: 48129

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

Upvotes: 1

John Woo
John Woo

Reputation: 263683

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

Upvotes: 2

Related Questions