Custom Order in Oracle SQL

Don't know if this qualifies as simple:

order by 
    case 
       when currency = 'USD' then 1 
       when currency = 'BHT' then 2
       when currency = 'JPY' then 3
       when currency = 'MYR' then 4
       else 5
    end

or a bit more compact but Oracle specific:

order by decode(currency, 'USD', 1, 'BHT', 2, 'JPY', 3, 'MYR', 4, 5)

The above solution using numbers to defined the sort order will not automatically sort currencies correctly that aren't mentioned in the case/decode expression.

To simply put USD at the front and don't care about the rest, the "generated" order criteria must be a character value as well. You can use the following in that case:

order by 
    case 
       when currency = 'USD' then '001' 
       else currency
    end

Which uses an "alphabetical" ordering. This works because characters are sorted after the number digits. (Using 'AAA' instead of '001' would work as well).


A more detailed way of doing this, if you are interesting in sorting certain values to the beginning or end, but have those sorted in their group:

order by
  case when currency in ('USD', 'CAD') 
    then '000'||currency
  when currency in ('ZWD', 'HTG')
    then 'ZZZ'||currency
  else currency 
  end

This will put the USD and CAD at the top of the list (sorted), ZWD and HTG at the bottom, and the rest sorted between those.


Maybe this will help you:

order by decode(currency, 'USD', 1, 2)

or using case

  order by 
      case 
        when currency = 'USD' then 1 
        else 2
      end

To make sure Your sort is "flexible" and will work with all currencies do this:

SELECT <columns>
FROM <tableName>
ORDER BY DECODE(currencyColumn,'USD', 1, 2), currencyColumn