Order by string values
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Order by string values

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Question

    Hi,

    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!

  2. #2
    Join Date
    Feb 2001
    Posts
    123
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width