Custom Order in Oracle SQL

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).

I need to do a Custom Oracle Sort

select *
  from db_table
 order by "date", 
    case 
       when area = 'top' then 1 
       when area = 'middle' then 2
       when area = 'bottom' then 3
       else 4
    end;

Refer to Custom Sort Order

Custom order in Oracle PL/SQL

ORDER BY DECODE(
         GRADE_MASTER.GRADE_DESCRIPTION,
         'SENIOR DIRECTOR', 1,
         'DIRECTOR', 2,
         'MANAGER', 3,
         'EMPLOYEE', 4,
         5)

Custom order by - Oracle SQL

marking my comment as an answer.

You're looking for

select * from table1 order by id, sequence, col_id

This gives your desired result. All col_ids sorted with sequence 1 which is within id 1. and so on.

SQL Order By in oracle custom order by

One method is to use case:

order by (case when firstname = 'nexus' then 1
               when firstname = 'samsumg' then 2
               when firstname = 'apple' then 3
          end)

Another method that has less typing:

order by instr(',nexus,samsung,apple,', ',' || name || ',')

Custom sorting in oracle

You can use a DECODE (or CASE) statement (and could put meaningful values) and, since you are getting a 3-character sub-string, you need to include the trailing space:

If a simplified version of your query is:

SELECT SUBSTR( name, 9, 3 ) AS tenor,
       name
FROM   table_name
ORDER BY SUBSTR( name, 1, 8 ),
         DECODE(
           tenor,
           'ON ', 1/30,
           'SW ', 7/30,
           '1M ', 1,
           '2M ', 2,
           '3M ', 3,
           '6M ', 6,
           '9M ', 9,
           '1Y ', 12,
           '18M', 18,
           '2Y ', 24,
           '3Y ', 36,
           '5Y ', 60,
           '10Y', 120,
           NULL
         ) ASC NULLS LAST

For the test data:

CREATE TABLE table_name ( name ) AS
SELECT 'ARZ USD 18M FX FORWARD' FROM DUAL UNION ALL
SELECT 'BRZ USD 10Y FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 1Y FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 2M FX FORWARD' FROM DUAL UNION ALL
SELECT 'BRZ USD 1M FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 6M FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 3M FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 1M FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 9M FX FORWARD' FROM DUAL UNION ALL
SELECT 'BRZ USD 1Y FX FORWARD' FROM DUAL;

This outputs:

TENOR | NAME                  
:---- | :---------------------
1M    | ARZ USD 1M FX FORWARD 
2M    | ARZ USD 2M FX FORWARD 
3M    | ARZ USD 3M FX FORWARD 
6M    | ARZ USD 6M FX FORWARD 
9M    | ARZ USD 9M FX FORWARD 
1Y    | ARZ USD 1Y FX FORWARD 
18M   | ARZ USD 18M FX FORWARD
1M    | BRZ USD 1M FX FORWARD 
1Y    | BRZ USD 1Y FX FORWARD 
10Y   | BRZ USD 10Y FX FORWARD

db<>fiddle here

Custom order by

You need to write an expression that extracts the date from the string and then use that expression in your ORDER BY.

An easy way to do that, with your sample data, might be:

select * from files
order by substr(file_name,-15);

I.e., sort by the last 15 positions of the filename (which are the date).

If you are on 12.2 or later, you can do a little better with this:

select * from files
order by to_date(substr(file_name,-15) 
             default null on conversion error,'YYYYMMDD"_"HH24MISS') nulls last;

This will make sure the data you are sorting on are actual dates. Any file name in your last that does not have a date stamp will be sorted last.

Oracle SQL - Custom Sort

This is how I understood the problem; sample data till line #7; query begins at line #8.

SQL> with locations (id, type) as
  2    (select 1000 , 'STORE'     from dual union all
  3     select 11001, 'STORE'     from dual union all
  4     select 20000, 'STORE'     from dual union all
  5     select 1181 , 'WAREHOUSE' from dual union all
  6     select 12002, 'STORE'     from dual
  7    )
  8  select id, type
  9  from locations
 10  order by case when substr(to_char(id), -3) = '000' then 1 end,
 11           case when type = 'WAREHOUSE' then 2 end,
 12           type;

        ID TYPE
---------- ---------
      1000 STORE
     20000 STORE
      1181 WAREHOUSE
     12002 STORE
     11001 STORE

SQL>

ORACLE - Custom ORDER BY to order pairs of data rows

You can ORDER BY a group sum first, like this

ORDER BY
   MAX(AMOUNT) OVER (PARTITION BY VOUCHER_NO) DESC, -- voucher with highest amount first
   VOUCHER_NO,  -- all rows of that voucher
   CASE WHEN JOURNAL_TYPE = 'CREDIT' THEN 0 ELSE 1 END, -- credit first
   AMOUNT DESC

---

Related Topics