SQL - Order By Clause

SQL - Sorting Results with ORDER BY

The SQL ORDER BY clause is used to sort the rows in a result set based on the values in one or more columns. Without an ORDER BY clause, the relational database system does not guarantee any specific order for the returned rows. If you need results in a predictable sequence, you must use ORDER BY.

The ORDER BY Clause Syntax

ORDER BY is specified at the end of a SELECT statement, after the FROM, WHERE, GROUP BY, and HAVING clauses.

SELECT column_list
FROM table_name
[WHERE condition]
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

• ASC: Sorts the results in ascending order (A to Z, 0 to 9). This is the default behavior if no order is specified.
• DESC: Sorts the results in descending order (Z to A, 9 to 0).

Setup: Sample Data

For our examples, let’s use an EMPLOYEES table.

CREATE TABLE EMPLOYEES (
    ID INT PRIMARY KEY,
    FIRST_NAME VARCHAR(50) NOT NULL,
    LAST_NAME VARCHAR(50) NOT NULL,
    DEPARTMENT VARCHAR(50),
    SALARY DECIMAL(10, 2)
);

INSERT INTO EMPLOYEES VALUES
(1, 'Alice', 'Smith', 'Engineering', 90000.00),
(2, 'Bob', 'Johnson', 'Engineering', 85000.00),
(3, 'Charlie', 'Brown', 'Marketing', 70000.00),
(4, 'Diana', 'Prince', 'Marketing', 72000.00),
(5, 'Ethan', 'Hunt', NULL, 110000.00);

Single-Column Sorting

This is the simplest form of sorting, based on a single column.

Example: Sort by Salary (Descending)

SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES
ORDER BY SALARY DESC;

This query lists employees from the highest paid to the lowest paid.

Multi-Column (Hierarchical) Sorting

You can sort by multiple columns. The database first sorts the results by the first column specified. Then, for any rows that have the same value in the first column, it sorts them by the second column, and so on.

Example: Sort by Department, then by Salary

SELECT FIRST_NAME, DEPARTMENT, SALARY FROM EMPLOYEES
ORDER BY DEPARTMENT ASC, SALARY DESC;

This query will first group employees by department (alphabetically), and then within each department, it will list them from highest to lowest salary.

FIRST_NAME	DEPARTMENT	SALARY
Alice	Engineering	90000.00
Bob	Engineering	85000.00
Diana	Marketing	72000.00
Charlie	Marketing	70000.00
Ethan		110000.00

Sorting with NULL Values

How NULL values are sorted depends on the database system. Some place NULLs first, others place them last. Standard SQL provides the NULLS FIRST and NULLS LAST options for explicit control (supported in PostgreSQL and Oracle).

Example (PostgreSQL/Oracle)

-- List departments, ensuring NULLs appear at the end
SELECT DEPARTMENT FROM EMPLOYEES
ORDER BY DEPARTMENT ASC NULLS LAST;

In MySQL and SQL Server, you can achieve this with a CASE statement or a trick like ORDER BY -column DESC for numeric types.

Custom Sorting with CASE

Sometimes you need to sort based on a non-standard, business-defined order. The CASE statement within ORDER BY is perfect for this.

Example: Sort by a specific department order

SELECT FIRST_NAME, DEPARTMENT FROM EMPLOYEES
ORDER BY
  CASE DEPARTMENT
    WHEN 'Engineering' THEN 1
    WHEN 'Marketing'   THEN 2
    ELSE 3
  END;

This query forces ‘Engineering’ to come first, followed by ‘Marketing’, and then all other departments.

Performance Considerations

Sorting can be a resource-intensive operation, especially on large tables. Performance can be significantly improved by creating a database index on the column(s) you are sorting by.

Pro Tip: If a query with ORDER BY is slow, the first step in optimization is to check if an index exists on the sorting columns. For a multi-column sort like ORDER BY a, b, a composite index on (a, b) is most effective.

-- This index would speed up our multi-column sort example
CREATE INDEX idx_employees_dept_salary ON EMPLOYEES (DEPARTMENT, SALARY);