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 1WHEN 'UIV' THEN 2WHEN 'NUIV' THEN 3ELSE 4END WHEN 'Private Equity' THEN CASE [AccountType]WHEN 'FUT' THEN 5WHEN 'UIV' THEN 6WHEN 'NUIV' THEN 7ELSE 8END ... ...WHEN 'Government Bond' THEN CASE [AccountType]WHEN 'FUT' THEN 17WHEN 'UIV' THEN 18WHEN 'NUIV' THEN 19ELSE 20END END