I have a table of values as shown below:
EMPNO ENAME JOB
---------- ---------- ---------
7788 SCOTT ANALYST
7902 FORD ANALYST
7369 SMITH CLERK
7876 ADAMS CLERK
7934 MILLER CLERK
7900 JAMES CLERK
7566 JONES MANAGER
7782 CLARK MANAGER
7698 BLAKE MANAGER
7839 KING PRESIDENT
7499 ALLEN SALESMAN
7654 MARTIN SALESMAN
7844 TURNER SALESMAN
7521 WARD SALESMAN
I want to order by job column in such a way that it will list Clerk, President, Analyst, Saleman then Manager. Is there such a sql comand which i can use to order the values of a column?? Please help..... Thanks!
ORDER BY decode(job,'Clerk',1, 'President',2, 'Analyst',3, 'Salesman',4,'Manager',5,6)
might be what you are looking for.
Be wary of using this sort of construct though - if the jobs change, then you will have to recode any select statements that use it. I have included a default value of 6 so that any jobs that are not listed in the decode will appear at the end of the sort.
It might be better to have a jobs table, and replace the job name in your table with a job id that refers to jobs. Then you could have a sort priority column in the jobs table, and use that to order your results. This means that if jobs are added, the sort order can be changed without recoding anything.
Click Here to Expand Forum to Full Width