Recipe2.6.Sorting on a Data Dependent Key

Recipe 2.6. 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