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