Advanced Ordering

You have likely used ORDER BY to sort results by a single column. In this lesson, you will learn more powerful sorting techniques: ordering by multiple columns, controlling NULL placement, and sorting by expressions.

Quick Review: Basic ORDER BY

SELECT name, salary FROM employees
ORDER BY salary;

By default, ORDER BY sorts in ascending order (ASC). You can make this explicit or reverse it:

-- Ascending (default)
SELECT name, salary FROM employees ORDER BY salary ASC;

-- Descending
SELECT name, salary FROM employees ORDER BY salary DESC;

Sorting by Multiple Columns

When you sort by a single column, rows that share the same value in that column appear in an unpredictable order. Multi-column sorting lets you define a tiebreaker:

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

This query:

1. First sorts all employees by department alphabetically (A to Z)
2. Within each department, sorts by salary from highest to lowest

Each column in the ORDER BY list can have its own direction:

SELECT name, department, hire_date
FROM employees
ORDER BY department ASC, hire_date DESC;

This sorts by department A-Z, and within each department, shows the most recently hired employee first.

Three or More Sort Columns

You can add as many sort columns as you need:

SELECT name, department, is_active, salary
FROM employees
ORDER BY department ASC, is_active DESC, salary DESC;

This sorts by department first, then active employees before inactive ones within each department, then by salary within each activity status.

Sorting with NULLS FIRST and NULLS LAST

By default, PostgreSQL places NULL values last in ascending order and first in descending order. You can override this behavior:

-- NULLs appear at the top
SELECT name, manager_id
FROM employees
ORDER BY manager_id ASC NULLS FIRST;

-- NULLs appear at the bottom, even in descending order
SELECT name, manager_id
FROM employees
ORDER BY manager_id DESC NULLS LAST;

This is especially useful when NULL values carry meaning. For example, employees with no manager (NULL manager_id) might be top-level executives you want to see first.

Default NULL Behavior Summary

Sorting by Column Position

Instead of column names, you can reference columns by their position in the SELECT list (starting at 1):

SELECT name, department, salary
FROM employees
ORDER BY 2, 3 DESC;

This is equivalent to ORDER BY department, salary DESC. The 2 refers to the second column in the SELECT list (department), and 3 refers to the third (salary).

Note: While this is convenient for quick queries, using column names is clearer and less error-prone in production code. If someone reorders the SELECT columns, positional references silently change their meaning.

Sorting by Expressions

You can sort by calculated values, not just raw columns:

SELECT name, salary
FROM employees
ORDER BY salary * 12 DESC;

This sorts employees by their annual salary (assuming salary stores monthly values) from highest to lowest.

Sorting by String Length

SELECT name FROM employees
ORDER BY LENGTH(name) DESC;

This shows employees with the longest names first.

Sorting by Date Parts

SELECT name, hire_date
FROM employees
ORDER BY EXTRACT(MONTH FROM hire_date), EXTRACT(DAY FROM hire_date);

This sorts employees by the month and day they were hired, regardless of the year -- useful for finding work anniversaries.

Sorting by CASE Expressions

You can create custom sort orders using CASE:

SELECT name, department
FROM employees
ORDER BY
    CASE department
        WHEN 'Engineering' THEN 1
        WHEN 'Sales' THEN 2
        WHEN 'Marketing' THEN 3
        ELSE 4
    END;

This lets you define an arbitrary order for department names instead of alphabetical sorting.

Sorting by Columns Not in SELECT

You can sort by a column that is not in your SELECT list:

SELECT name, department FROM employees
ORDER BY salary DESC;

This returns only names and departments, but orders the result by salary. The salary column is used for sorting but does not appear in the output.

Try It Yourself

1. List all products sorted by category ascending, then by price descending within each category.

SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

2. List employees sorted by their manager_id, with employees who have no manager (NULL) appearing first.

SELECT name, department, manager_id
FROM employees
ORDER BY manager_id ASC NULLS FIRST;

3. List all orders sorted by status (completed first, then pending, then cancelled), and by total_price descending within each status.

SELECT customer_name, status, total_price
FROM orders
ORDER BY
    CASE status
        WHEN 'completed' THEN 1
        WHEN 'pending' THEN 2
        WHEN 'cancelled' THEN 3
        ELSE 4
    END,
    total_price DESC;

4. List employees sorted by the year they were hired (oldest first), and alphabetically by name within each year.

SELECT name, hire_date
FROM employees
ORDER BY EXTRACT(YEAR FROM hire_date) ASC, name ASC;