ORDER BY

Objectives

By the end of this lesson, you should be able to:

• Sort query results ascending or descending
• Sort by more than one column
• Explain where NULL values land in a sorted result

💡 Why this matters: Without ORDER BY, a query’s row order isn’t guaranteed, PostgreSQL returns rows in whatever order is convenient internally, not necessarily the order they were inserted. ORDER BY makes the order explicit and predictable.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier in this module.

Sorting Ascending and Descending

SELECT first_name, salary FROM employees ORDER BY salary;

 first_name | salary
------------+---------
 Devon      | 58000.00
 Jordan     | 61000.00
 Sam        | 63000.00
 Maya       | 67000.00
 Alexis     | 71000.00
 Erin       | 74000.00
 Priya      | 89000.00
 Taylor     | 95000.00

ORDER BY column sorts ascending by default, lowest to highest. Add DESC for descending:

SELECT first_name, salary FROM employees ORDER BY salary DESC;

 first_name | salary
------------+---------
 Taylor     | 95000.00
 Priya      | 89000.00
 Erin       | 74000.00
 Alexis     | 71000.00
 Maya       | 67000.00
 Sam        | 63000.00
 Jordan     | 61000.00
 Devon      | 58000.00

ASC also exists, for ascending, but since it’s the default, it’s rarely written explicitly.

Sorting by Multiple Columns

SELECT first_name, department, salary FROM employees
ORDER BY department, salary DESC;

 first_name | department  | salary
------------+-------------+---------
 Taylor     | Engineering | 95000.00
 Priya      | Engineering | 89000.00
 Erin       | Engineering | 74000.00
 Alexis     | Marketing   | 71000.00
 Maya       | Marketing   | 67000.00
 Sam        | Sales       | 63000.00
 Jordan     | Sales       | 61000.00
 Devon      |             | 58000.00

This sorts by department first (alphabetically, ascending, the default), and within each department, by salary descending. Each column listed can have its own direction, ORDER BY department DESC, salary ASC would sort departments Z-to-A, with ascending salary inside each one.

Where NULL Lands

Notice Devon (whose department is NULL) sorted to the very end, even though this is an ascending sort by department. PostgreSQL’s default is NULL sorts last in ascending order, and first in descending order. This can be controlled explicitly with NULLS FIRST or NULLS LAST, added after the column, but the default is worth knowing on its own.

Try It

1. Write a query that sorts employees by hire_date, earliest first.
2. Write a query that sorts employees by salary, highest first.
3. Write a query that sorts by department ascending, and within each department, by first_name ascending.
4. Explain, in your own words, where a NULL value lands in an ascending sort, without running anything, then confirm by running a query that would show it.

Recap

• ORDER BY column sorts ascending by default, add DESC for descending.
• Listing multiple columns sorts by the first, then breaks ties using the next, each column can have its own direction.
• NULL sorts last in ascending order and first in descending order, by default.

Next lesson: LIMIT and OFFSET, controlling how many rows come back, and from where.