Using "case ....end" in order by

Schema : scott/tiger
Version : Oracle 10g

Say your manager stops by and asks you to produce a result like this, from the standard "emp" table :

DEPTNO ENAME JOB
10 CLARK MANAGER
10 MILLER CLERK
10 KING PRESIDENT
20 JONES MANAGER
20 SCOTT ANALYST
20 FORD ANALYST
20 SMITH CLERK
20 ADAMS CLERK
30 BLAKE MANAGER
30 JAMES CLERK
30 WARD SALESMAN
30 ALLEN SALESMAN
30 TURNER SALESMAN
30 MARTIN SALESMAN

So, if you notice, for each department the manager records shows up first and then the rest of the records in the department are sorted by Job.

So, how do we get this result ?

Once again, Analytics rock and roll !!

Here we go :

select e.deptno, e.ename, e.job
from scott.emp e
order by deptno,
case
when job = 'MANAGER' then
0
else
1
end;

This is how this query works :

Looking at the JOB column, there are jobs before and after 'MANAGER'. Eg : 'ANALYST' comes before 'MANAGER' and 'SALAESMAN' comes after the 'MANAGER'.
So, even if order by "asc" or "desc" we won't achieve the result. The magic is using "case ...end" in order by.
The statement : "case when job='MANAGER' then 0 else 1 end" says : if the job is 'MANAGER' then order by it otherwise, order by the job column.
So, within each partition(or group) the 'MANAGER' records becomes the first record.

Long live "Tom Kyte".

Good Luck !!
r-a-v-i