Sorting by Substrings - SQL SERVER

Problem

You want to sort the results of a query by specific parts of a string. For example, you want to return employee names and jobs from table EMP and sort by the last two characters in the job field. The result set should look like the following:

 ENAME       JOB
 ----------  ---------
 KING        PRESIDENT
 SMITH       CLERK
 ADAMS       CLERK
 JAMES       CLERK
 MILLER      CLERK
 JONES       MANAGER
 CLARK       MANAGER
 BLAKE       MANAGER
 ALLEN       SALESMAN
 MARTIN      SALESMAN
 WARD        SALESMAN
 TURNER      SALESMAN
 SCOTT       ANALYST
 FORD        ANALYST

Solution

DB2, MySQL, Oracle, and PostgreSQL

Use the SUBSTR function in the ORDER BY clause:

 select ename,job
   from emp
  order by substr(job,length(job)-2)

SQL Server

Use the SUBSTRING function in the ORDER BY clause:

 select ename,job
   from emp
  order by substring(job,len(job)-2,2)

Discussion

Using your DBMS's substring function, you can easily sort by any part of a string. To sort by the last two characters of a string, find the end of the string (which is the length of the string) and subtract 2. The start position will be the second to last character in the string. You then take all characters after that start position. Because SQL Server requires a third parameter in SUBSTRING to specify the number of characters to take. In this example, any number greater than or equal to 2 will work.

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

Sorting by Multiple Fields

Problem

You want to sort the rows from EMP first by DEPTNO ascending, then by salary descending. You want to return the following result set:

      EMPNO      DEPTNO         SAL  ENAME       JOB
 ----------  ----------  ----------  ----------  ---------
       7839          10        5000  KING        PRESIDENT
       7782          10        2450  CLARK       MANAGER
       7934          10        1300  MILLER      CLERK
       7788          20        3000  SCOTT       ANALYST
       7902          20        3000  FORD        ANALYST
       7566          20        2975  JONES       MANAGER
       7876          20        1100  ADAMS       CLERK
       7369          20         800  SMITH       CLERK
       7698          30        2850  BLAKE       MANAGER
       7499          30        1600  ALLEN       SALESMAN
       7844          30        1500  TURNER      SALESMAN
       7521          30        1250  WARD        SALESMAN
       7654          30        1250  MARTIN      SALESMAN
       7900          30         950  JAMES       CLERK

Solution

List the different sort columns in the ORDER BY clause, separated by commas:

 1 select empno,deptno,sal,ename,job
 2   from emp
 3  order by deptno, sal desc

Discussion

The order of precedence in ORDER BY is from left to right. If you are ordering using the numeric position of a column in the SELECT list, then that number must not be greater than the number of items in the SELECT list. You are generally permitted to order by a column not in the SELECT list, but to do so you must explicitly name the column. However, if you are using GROUP BY or DISTINCT in your query, you cannot order by columns that are not in the SELECT list.

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

Returning Query Results in a Specified Order

Problem

You want to display the names, job, and salaries of employees in department 10 in order based on their salary (from lowest to highest). You want to return the following result set:

 ENAME       JOB               SAL
 ----------  ---------  ----------
 MILLER      CLERK            1300
 CLARK       MANAGER          2450
 KING        PRESIDENT        5000

Solution

Use the ORDER BY clause:

 1 select ename,job,sal
 2   from emp
 3  where deptno = 10
 4  order by sal asc

Discussion

The ORDER BY clause allows you to order the rows of your result set. The solution sorts the rows based on SAL in ascending order. By default, ORDER BY will sort in ascending order, and the ASC clause is therefore optional. Alternatively, specify DESC to sort in descending order:

  select ename,job,sal
   from emp
  where deptno = 10
  order by sal desc

 ENAME       JOB               SAL
 ----------  ---------  ----------
 KING        PRESIDENT        5000
 CLARK       MANAGER          2450
 MILLER      CLERK            1300

You need not specify the name of the column on which to sort. You can instead specify a number representing the column. The number starts at 1 and matches the items in the SELECT list from left to right. For example:

 
 select ename,job,sal
   from emp
  where deptno = 10
  order by 3 desc

 ENAME       JOB               SAL
 ----------  ---------  ----------
 KING        PRESIDENT        5000
 CLARK       MANAGER          2450
 MILLER      CLERK            1300

The number 3 in this example's ORDER BY clause corresponds to the third column in the SELECT list, which is SAL.

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