conditional order by clause in sql

Question

I have a query that should order the result in asc or desc depending upon a column value.

e.g.

if employee of type manager exists THEN order by joining_date, bith_date ASC else if employee is developer THEN order by joining_date, birth_date DESC.

I would like to achieve something like below, but can't achieve that.

ORDER BY CASE WHEN employee_type = 'm'  
              THEN joining_date, birth_date ASC;
              WHEN employee_type = 'd' 
              THEN joining_date, birth_date  DESC; 

Answer 1

Well I got the answer after some research.

We can add multiple columns in where clause conditionally as follows :

ORDER BY DECODE(employee_type, 'm', joining_date, birth_date, salary) ASC,
         DECODE(employee_type, 'd', joining_date, birth_date, salary) DESC

This will order the result on the basis of employee_type.

Answer 2

I suspect you want something like this:

ORDER BY 
    employee_type DESC             -- first all the managers, then the developers
                                   -- and in every one of these two groups
  , joining_date                   -- first order by joining date
  , CASE WHEN employee_type = 'm'  -- and then either by
        THEN birth_date            -- birth date ascending for managers
        ELSE NULL
    END                            -- or
  , birth_date DESC ;              -- birth date descending for the rest (devs)