The Staff table is being used for the following queries.
Staff Table
| empno | ename | job | mgr_no | hiredate | sal | commission | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | ALI | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | RAHUL | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | RAJ | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAM | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | ARHAM | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
1. Returning query results in a specified order
You want to display the names, jobs, and salaries of employees in department 10 in
order based on their salary (from lowest to highest).
Use the ORDER BY clause:
SELECT ename,job,sal
FROM Staff
WHERE deptno = 10
ORDER BY sal asc
Output
| ename | job | sal |
|---|---|---|
| MILLER | CLERK | 1300 |
| CLARK | MANAGER | 2450 |
| RAJ | PRESIDENT | 5000 |
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.
2. Sorting by multiple fields
You want to sort the rows from Staff first by deptno ascending, then by salary
descending.
List the different sort columns in the ORDER BY clause, separated by commas:
SELECT empno,deptno,sal,ename,job
FROM Staff
ORDER BY deptno, sal desc
Output
| empno | deptno | sal | ename | job |
|---|---|---|---|---|
| 7839 | 10 | 5000 | RAJ | PRESIDENT |
| 7782 | 10 | 2450 | CLARK | MANAGER |
| 7934 | 10 | 1300 | MILLER | CLERK |
| 7788 | 20 | 3000 | RAHUL | ANALYST |
| 7902 | 20 | 3000 | ARHAM | ANALYST |
| 7566 | 20 | 2975 | JONES | MANAGER |
| 7876 | 20 | 1100 | ADAM | CLERK |
| 7369 | 20 | 800 | ALI | 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 |
The order in which columns are listed in the ORDER BY clause determines the precedence of the sorting. When using the numeric position of a column in the SELECT list to specify the sorting order, the number must not exceed the number of columns in the SELECT list. It is possible to order by columns that are not in the SELECT list, but the column must be explicitly named in the ORDER BY clause. If the query includes a GROUP BY or DISTINCT clause, it is not possible to order by columns that are not in the SELECT list.
3. Sorting by Substrings
If you want to sort the results of a query by a specific part of a string, you can use the SUBSTRING function to extract that part of the string and use it in the ORDER BY clause. For example, to return employee names and jobs from the Staff table and sort by the last two characters in the JOB field, you could use the following query:
DB2, MySQL, Oracle, and PostgreSQL
Use the SUBSTR function in the ORDER BY clause:
SELECT ename,job
FROM Staff
ORDER BY substr(job,length(job)-1)
Output
| ename | job |
|---|---|
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| RAJ | PRESIDENT |
| ALI | CLERK |
| ADAM | CLERK |
| JAMES | CLERK |
| MILLER | CLERK |
| RAHUL | ANALYST |
| ARHAM | ANALYST |
SQL Server
Use the SUBSTRING function in the ORDER BY clause:
SELECT ename,job
FROM Staff
ORDER BY substring(job,len(job)-1,2)
Output
| ename | job |
|---|---|
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| RAJ | PRESIDENT |
| ALI | CLERK |
| ADAM | CLERK |
| JAMES | CLERK |
| MILLER | CLERK |
| RAHUL | ANALYST |
| ARHAM | ANALYST |
Discussion
You can quickly sort by any component of a string using the substring function in your DBMS. Find the end of the string (which is the length of the string) and deduct two to sort by the final two characters of the string. The next-to-last character in the string will represent the start position. Then, you take all characters that come following that initial location. The SUBSTRING function in SQL Server differs from the SUBSTR function in that it needs a third parameter to indicate how many characters to accept. Any integer more than or equal to two will function in this example.
Code Runner
Copy the below queries in the code runner to see the result
SELECT ename,job,sal
FROM Staff
WHERE deptno = 10
ORDER BY sal asc
SELECT empno,deptno,sal,ename,job
FROM Staff
ORDER BY deptno, sal desc
SELECT ename,job
FROM Staff
ORDER BY substr(job,length(job)-1)