Oracle SQL – Custom Sort

I have a scenario where I have following data:

Table: Locations

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

I want to sort in a way that all the IDs that end in ‘0000’ should be sorted first, then the TYPE ‘Warehouse’ and then the rest of the Stores.

The desired output should be like

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

How do I do this custom sorting?

Answer

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>