Conditional order by condition in sql

Question

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

eg.

if the employee of the type manager exists THEN by doing joining_date, bith_date ASC otherwise, if the employee is a developer, then order by joining_date, birth_date DESC.

I would like to achieve something like below, but I cannot achieve this.

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 an answer after some research.

We can add several columns to the 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 based on 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) 

Answer 3

The question is a little asked.

order the result in asc or desc depending on the value of the column.

A column takes many values ​​(since there are several rows).

Now the order by clause uses the expression and orders the lines on it. This expression must be morphotropic (;))

So, assuming a stardard oracle employee schema, managers:

 select * from emp e where exists (select emp_id from emp where e.id=emp.mgr_id) 

A workaround may be:

 Select e.id, e.name, e.birth_date, case when (select count(*) from emp e where exists (select emp_id from emp where e.id=emp.mgr_id) ) --existence of manager > 0 then birth_date - to_date('1-Jan-1000','dd-mon-yyyy') else to_date('1-Jan-1000','dd-mon-yyyy') - birth_date end as tricky_expression from emp A order by 4; 

This exexpresion is a case ; Using a constant (a subquery that decides that there are managers), it changes the values ​​from positive to negative, i.e. Changes the direction of the order.

UPDATE: with information in the comments:

 select id, name, birth_date emp_type from ( Select id, name, birth_date, emp_type, case when cnt_mgr > 0 then birth_date - to_date('1-Jan-1000','dd-mon-yyyy') else to_date('1-Jan-1000','dd-mon-yyyy') - birth_date end as tricky_expression from( Select e.id, e.name, e.birth_date, emp_type, count(case when emp_type='M' then 1 else 0 end) over() as mgr_count from emp A where your_conditions ) order by tricky_expression ) where rownum=1; 

Answer 4

If the company has a manager, this request returns the oldest manager, otherwise - the youngest developer.

 select id, name, birth_date, emp_type from emp where id = (select max(id) keep (dense_rank first order by decode(emp_type, 'M', 1, 'D', 2), joining_date, decode(emp_type, 'M', 1, 'D', -1) * (birth_date - to_date('3000','yyyy'))) from emp)