Oracle SQL - Custom Sort

Question

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 1

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>

Answer 2

You can use a case expression in sorting:

order by (case when id like '%0000' then 1
               when type = 'WAREHOUSE' then 2
               else 3
          end), id

This also uses id to sort within the three groups.

Note: If id is a number and not a string, I would suggest:

order by (case when mod(id, 10000) = 0 then 1
               when type = 'WAREHOUSE' then 2
               else 3
          end), id

---

[EDITED by LF]

This what your ORDER BY returns, and that's not what the OP wanted:

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 id like '%0000' then 1
 11                 when type = 'WAREHOUSE' then 2
 12                 else 3
 13            end), id;

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

SQL>

Comment by Gordon: The above should work if the 1000 row is 10000.