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 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 can not order by columns that are not in the SELECT list.

3. Sorting by Substrings

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 Staff and sort by the last two characters in the JOB field.

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

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 two. The start position will be the second to last character in the string. You then take all characters after that start position. SQL Server’s SUBSTRING is different from the SUBSTR function as it requires a third parameter that specifies how many characters to take. In this example, any number greater than or equal to two will work.