# Explain the sorting query techniques in SQL

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)
``````