Recipe 2.6. Sorting on a Data Dependent KeyProblemYou 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 SolutionUse 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 DiscussionYou 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 |