Sorting on a Data Dependent Key

Problem

You want to sort based on some conditional logic. For example: if JOB is "SALESMAN" you want to sort on COMM; otherwise, you want to sort by SAL. You want to return the following result set:

 ENAME             SAL JOB             COMM
 ---------- ---------- --------- ----------
 TURNER           1500  SALESMAN          0
 ALLEN            1600  SALESMAN        300
 WARD             1250  SALESMAN        500
 SMITH             800  CLERK
 JAMES             950  CLERK
 ADAMS            1100  CLERK
 MARTIN           1250  SALESMAN       1300
 MILLER           1300  CLERK
 CLARK            2450  MANAGER
 BLAKE            2850  MANAGER
 JONES            2975  MANAGER
 SCOTT            3000  ANALYST
 FORD             3000  ANALYST
 KING             5000  PRESIDENT

Solution

Use a CASE expression in the ORDER BY clause:

 1 select ename,sal,job,comm
 2   from emp
 3  order by case when job = 'SALESMAN' then comm else sal end

Discussion

You can use the CASE expression to dynamically change how results are sorted. The values passed to the ORDER BY look as follows:

 
 select ename,sal,job,comm,
        case when job = 'SALESMAN' then comm else sal end as ordered
   from emp
  order by 5

 ENAME             SAL JOB             COMM    ORDERED
 ---------- ---------- --------- ---------- ----------
 TURNER           1500 SALESMAN           0          0
 ALLEN            1600 SALESMAN         300        300
 WARD1             250 SALESMAN         500        500
 SMITH             800 CLERK                       800
 JAMES             950 CLERK                       950
 ADAMS            1100 CLERK                      1100
 MARTIN           1250 SALESMAN        1300       1300
 MILLER           1300 CLERK                      1300
 CLARK2            450 MANAGER                    2450
 BLAKE2            850 MANAGER                    2850
 JONES2            975 MANAGER                    2975
 SCOTT            3000 ANALYST                    3000
 FORD             3000 ANALYST                    3000
 KING             5000 PRESIDENT                  5000

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table