Sorting Data with SQL: A Complete Guide to the ORDER BY Clause

This article provides a comprehensive explanation of the ORDER BY clause used in Oracle SQL. Sorting is an essential skill in database operations. To efficiently prepare data for analysis and reporting based on business requirements, we will introduce practical techniques with concrete examples. Using the SCOTT schema, this guide is useful for both SQL beginners and professionals.

You can install the SCOTT schema by executing the following:

SQL> @?/rdbms/admin/utlsampl.sql

1. Purpose and Basic Syntax of the ORDER BY Clause
2. Basic Sorting for Business Data – Ascending and Descending Order
3. Sorting with Multiple Conditions – Flexible Data Presentation
4. Handling NULL Values and Customizing Sorting
5. Sorting by Calculated Columns Using Aliases
6. Sorting by Column Position for Simpler Queries
7. Custom Sorting with CASE Expressions
8. Optimizing ORDER BY Clause for Performance
Summary

1. Purpose and Basic Syntax of the ORDER BY Clause

The ORDER BY clause is used to sort the results of a query in a specified order. While it is a basic feature of Oracle SQL, combining it in different ways allows for a wide range of sorting possibilities.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY sort_column [ASC|DESC];

ASC (Ascending): The default. Sorts numbers from smallest to largest, and strings in alphabetical order.
DESC (Descending): Sorts numbers from largest to smallest, and strings in reverse alphabetical order.

2. Basic Sorting for Business Data – Ascending and Descending Order

For example, if a sales representative wants to display a list of customers ordered by their monthly contract fee in descending order:

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
ORDER BY SAL DESC;

This query displays employees in order of highest salary, allowing quick access to VIP customers or high-priority data. Conversely, use ASC to list from lower to higher values.

SQL> SELECT EMPNO, ENAME, JOB, SAL
  2  FROM EMP
  3  ORDER BY SAL DESC;

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

12 rows selected.

3. Sorting with Multiple Conditions – Flexible Data Presentation

The ORDER BY clause can apply to multiple columns. For instance, to sort employees by department (DEPTNO) and job title (JOB):

SELECT EMPNO, ENAME, DEPTNO, JOB, SAL
FROM EMP
ORDER BY DEPTNO, JOB DESC, SAL DESC;

This query groups records by department and within each job title, orders by descending salary. Sorting by multiple columns helps in organizing detailed data.

SQL> SELECT EMPNO, ENAME, DEPTNO, JOB, SAL
  2  FROM EMP
  3  ORDER BY DEPTNO, JOB DESC, SAL DESC;

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

12 rows selected.

4. Handling NULL Values and Customizing Sorting

By controlling the position of NULL values in the ORDER BY clause, you can improve data consistency. Normally, NULL appears last in ascending and first in descending order. You can change this using NULLS FIRST or NULLS LAST.

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
ORDER BY SAL DESC NULLS LAST;

This displays employees in descending salary order, placing rows with NULL salary at the end—useful for identifying incomplete data.

SQL> SELECT EMPNO, ENAME, JOB, SAL
  2  FROM EMP
  3  ORDER BY SAL DESC NULLS LAST;

(Same output as previous descending order)

5. Sorting by Calculated Columns Using Aliases

For management data or summary reports, sorting by calculated columns is often necessary. You can use column aliases to simplify this.

SELECT EMPNO, ENAME, SAL*12 AS ANNUAL_SALARY
FROM EMP
ORDER BY ANNUAL_SALARY DESC;

This calculates annual salary and sorts the data in descending order—helpful for quickly identifying top earners.

SQL> SELECT EMPNO, ENAME, SAL*12 AS ANNUAL_SALARY
  2  FROM EMP
  3  ORDER BY ANNUAL_SALARY DESC;

EMPNO ENAME     ANNUAL_SALARY
----- ---------- --------------
7839  KING            60000
7902  FORD            36000
7566  JONES           35700
7698  BLAKE           34200
7782  CLARK           29400
7499  ALLEN           19200
7844  TURNER          18000
7934  MILLER          15600
7654  MARTIN          15000
7521  WARD            15000
7900  JAMES           11400
7369  SMITH            9600

12 rows selected.

6. Sorting by Column Position for Simpler Queries

Instead of using column names, you can specify column positions in the ORDER BY clause. This makes the query shorter, especially useful for large queries.

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
ORDER BY 2, 4 DESC;

This sorts by the second column (ENAME) ascending and fourth column (SAL) descending. However, using column numbers can reduce readability, so it’s best avoided in shared or long-term queries.

SQL> SELECT EMPNO, ENAME, JOB, SAL
  2  FROM EMP
  3  ORDER BY 2, 4 DESC;

(Same output, sorted by name and descending salary)

7. Custom Sorting with CASE Expressions

In some cases, you need to sort based on specific conditions. For example, prioritizing certain departments in display order:

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
ORDER BY 
  CASE 
    WHEN DEPTNO = 10 THEN 1
    WHEN DEPTNO = 20 THEN 2
    ELSE 3
  END, SAL DESC;

This query prioritizes department 10 first, then 20, and others after—useful when business requirements dictate display priority.

SQL> SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
  2  FROM EMP
  3  ORDER BY
  4    CASE
  5      WHEN DEPTNO = 10 THEN 1
  6      WHEN DEPTNO = 20 THEN 2
  7      ELSE 3
  8    END, SAL DESC;

(Same output sorted by department priority and salary)

8. Optimizing ORDER BY Clause for Performance

The use of ORDER BY can impact performance, especially with large datasets. Consider the following optimizations:

Use Indexes: Apply indexes to frequently sorted columns (e.g., SAL) to speed up processing.
Reduce Columns: Minimize the number of sorted columns to improve performance.
Use FETCH LIMITS: Limit output with FETCH FIRST N ROWS ONLY to shorten processing time.

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 10 ROWS ONLY;

This query displays the top 10 employees by salary, improving efficiency.

SQL> SELECT EMPNO, ENAME, JOB, SAL
  2  FROM EMP
  3  ORDER BY SAL DESC
  4  FETCH FIRST 10 ROWS ONLY;

EMPNO ENAME     JOB         SAL
----- ---------- ----------- ------
7839  KING      PRESIDENT    5000
7902  FORD      ANALYST      3000
7566  JONES     MANAGER      2975
7698  BLAKE     MANAGER      2850
7782  CLARK     MANAGER      2450
7499  ALLEN     SALESMAN     1600
7844  TURNER    SALESMAN     1500
7934  MILLER    CLERK        1300
7521  WARD      SALESMAN     1250
7654  MARTIN    SALESMAN     1250

10 rows selected.

Summary

Although ORDER BY is a basic SQL function, mastering it can greatly enhance business analysis and report generation. From basic usage to advanced techniques, leverage ORDER BY to make full use of your Oracle database.

[reference]
Sorting Query Results