Oracle sql order by with case statement

Question

I am facing difficulty in understanding oracle(12c) sql order by clause with case statement. 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? Could someone please explain this in detail?

Answer 1

If you want the department name in descending order, then you have to include that information in the query:

ORDER BY (CASE DEPT_NAME
              WHEN 'ACCOUNT' THEN 1
              WHEN 'AUDIT' THEN 2
              WHEN 'FINANCE' THEN 3
              ELSE 4
          END) DESC,
         DEPT_NAME DESC;

There is no reason for the value of the CASE to be a character string. The logic really calls for a number. If you use strings, then values larger than 9 will not work as you expect them to.

Answer 2

Try this with decode function, basically does the same thing.

SELECT DEPT_NO, DEPT_NAME 
  FROM SORTNG_LOGIC 
 ORDER BY 
decode (DEPT_NAME,'ACCOUNT','1','AUDIT','2','FINANCE','3','4') DESC;