Custom Order in Oracle SQL
SqlOracleSql Order-BySql Problem Overview
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 :
- BHT
- USD
- MYR
- JYP
should be sorted like :
- USD
- BHT
- JPY
- MYR
Is there a simple way to handle this?
Sql Solutions
Solution 1 - 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).
Solution 2 - Sql
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
Solution 3 - Sql
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.
Solution 4 - Sql
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
Solution 5 - Sql
One more variant with regexp like function FIELD()
in MySQL:
select
meas_code,
to_number(regexp_replace(meas_code, replace('(meas1,meas2,meas3)', ',', '|'), instr(replace('(meas1,meas2,meas3)', ',', '|'), meas_code))) ordr
from (select cast(column_value as varchar2(10)) as meas_code from xmltable('''meas1'',''meas2'',''meas3'''))
order by 2
Solution 6 - Sql
If you want a custom order i suggest you to use 'FIELD'.
SELECT * FROM table ORDER BY FIELD(column,"case 1","case 2",...);
Solution 7 - Sql
You could do the following:
SELECT
*
FROM
yourtable
ORDER BY
REPLACE(FIND_IN_SET(currency,'USD,BHT,JPY,MYR'),0,'Z')
Solution 8 - Sql
I needed to do the same, but with multiple columns, and found Grzegorz W's answer the best for this, with the following simple addition:
SELECT <columns>
FROM <tableName>
ORDER BY DECODE(currencyColumn,'USD', 1, 2), currencyColumn, anotherColumn;