I am facing difficulty in understanding oracle(12c) sql order by clause with case expression. I have a table with the below data,
SELECT DEPT_NO, DEPT_NAME FROM SORTNG_LOGIC;
DEPT_NO DEPT_NAME
---------- --------------------
1 FINANCE
2 ACCOUNT
3 HUMAN RESOURCE
4 AUDIT
5 TRAINING
I am executing the below SQL query for this table to add custom order, on Oracle SQL developer.
SELECT DEPT_NO, DEPT_NAME FROM SORTNG_LOGIC ORDER BY (
CASE DEPT_NAME
WHEN 'ACCOUNT' THEN '1'
WHEN 'AUDIT' THEN '2'
WHEN 'FINANCE' THEN '3'
ELSE '4' END
)DESC;
This is giving the below result:
DEPT_NO DEPT_NAME
---------- --------------------
3 HUMAN RESOURCE
5 TRAINING
1 FINANCE
4 AUDIT
2 ACCOUNT
But I expected that the result should be
DEPT_NO DEPT_NAME
---------- --------------------
5 TRAINING
3 HUMAN RESOURCE
1 FINANCE
4 AUDIT
2 ACCOUNT
As I am sorting the dept_name in descending order, I thought 'Training' should be above 'human resource'.
Where is my understanding going wrong?