SQL Case Order By specific order

Question

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!

Got an answer: CASE WHEN PortalName = 'Master Option' THEN 9999 ELSE 5 END — NJohns, Nov 02 '12 at 20:41

score 2 · Answer 1 · edited Nov 02 '12 at 13:37

2

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

edited Nov 02 '12 at 13:37

NJohns

53
2
2
7

answered Nov 02 '12 at 13:19

John Woo

258,903
69
498
492

Thanks, that made it cleaner, but it put Option D alphabetically in the set of Active options. – NJohns Nov 02 '12 at 13:29
@NJohns: Try swapping `WHEN IsActive = 1` and `WHEN PortalName = 'Master Option'` (entire lines) in this solution. – Andriy M Nov 03 '12 at 18:24

score 1 · Answer 2 · answered Nov 02 '12 at 13:45

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

Still not working. Mybe I will just see if this doesn't need to be a requirement ? :) — NJohns, Nov 02 '12 at 19:04

score -1 · Accepted Answer · answered Nov 02 '12 at 20:42

-1

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

answered Nov 02 '12 at 20:42

NJohns

53
2
2
7

1

why you had entered 9999. is it indicate column number...? then column no.9999 does n't exist – koushik veldanda Jan 21 '15 at 12:49

SQL Case Order By specific order

3 Answers3

Linked