I need to order transaction based on the currency. However I need to implement a custom order by, which makes the USD to always comes on the top, and the rest should be ordered asc.
for example :
should be sorted like :
Is there a simple way to handle this?
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).