Sunday, July 12, 2015

SQL Custom ORDER BY Multiple Columns

There're a lot of examples how to do custom ordering by one or multiple columns, but not for this particular case I'm working on. I need to do custom ordering based on a combination of static values across two tables.

   -------------------   ---------------
   | AssetClass      |   | AccountType |
   -------------------   ---------------
   | Equity          |   | FUT         |
   | Private Equity  |   | UIV         |
   | Credit          |   | NUIV        |
   | Macro           |   ---------------
   | Government Bond |
   -------------------

The query result should first be ordered by the AssetClass and then by the AccountType in the exact same order as shown above. Notice that neither is ordered alphabetically. An example of the result would look like something like this:

   ---------------------------------
   | AssetClass      | AccountType |
   ---------------------------------
   | Equity          | FUT         |        
   | Equity          | FUT         |
   | Equity          | FUT         |
   | Equity          | UIV         |
   | Equity          | UIV         |
   | Equity          | NUIV        |
   | Equity          | NUIV        |
   | Equity          | NUIV        |
   | Equity          | NUIV        |
   | Private Equity  | FUT         |
   | Private Equity  | FUT         |
   | Private Equity  | UIV         |
   | Private Equity  | UIV         |
   | Private Equity  | NUIV        |
   | Private Equity  | NUIV        |
   | ...             | ...         |
   | ...             | ...         |
   | ...             | ...         |
   | Government Bond | UIV         | 
   | Government Bond | UIV         |  
   | Government Bond | NUIV        |  
   | Government Bond | NUIV        |  
   | Government Bond | NUIV        |  
   ---------------------------------      
The ORDER BY statement is ugly but hey it does the job.
ORDER BY
    CASE [AssetClass]
        WHEN 'Equity' THEN
            CASE [AccountType]
                WHEN 'FUT' THEN 1
                WHEN 'UIV' THEN 2
                WHEN 'NUIV' THEN 3
                ELSE 4
            END
        WHEN 'Private Equity' THEN
            CASE [AccountType]
                WHEN 'FUT' THEN 5
                WHEN 'UIV' THEN 6
                WHEN 'NUIV' THEN 7
                ELSE 8
            END
        ...
        ...
        WHEN 'Government Bond' THEN
            CASE [AccountType]
                WHEN 'FUT' THEN 17
                WHEN 'UIV' THEN 18
                WHEN 'NUIV' THEN 19
                ELSE 20
            END
    END