ORDER BY

Rows returned by a query have no guaranteed order unless you ask for one. The ORDER BY clause sorts the result set of a SELECT statement by one or more columns, expressions, or even the numeric position of a column in the select list. It always runs after filtering (WHERE) and grouping, and is typically the last thing applied before LIMIT/OFFSET.

Basic sorting: ASC and DESC

Every column in an ORDER BY clause can be sorted in ascending order (the default) or descending order. You can mix directions per column when sorting by more than one.

Sort products by price, cheapest first

SQL

SELECT product_id, name, price
FROM products
ORDER BY price ASC;   -- ASC is the default, so "ORDER BY price" is identical

Sort products by price, most expensive first

SQL

SELECT product_id, name, price
FROM products
ORDER BY price DESC;

Sorting by multiple columns

List several columns separated by commas. PostgreSQL sorts by the first column, and only uses later columns to break ties within groups of equal values in the earlier ones.

Newest orders first, then highest total for same-day orders

SQL

SELECT order_id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, total_amount DESC;

Here every order is grouped by date (most recent date first), and within a single date, orders with a larger total come first.

Controlling where NULLs land

Real tables have missing data, and sorting has to put those NULLs somewhere. PostgreSQL lets you say exactly where with NULLS FIRST and NULLS LAST.

Customers without a phone number listed last

SQL

SELECT customer_id, first_name, phone
FROM customers
ORDER BY phone ASC NULLS LAST;

Explicitly put NULLs first instead

SQL

SELECT customer_id, first_name, phone
FROM customers
ORDER BY phone ASC NULLS FIRST;

PostgreSQL's default NULL ordering

Unlike some databases, PostgreSQL treats NULL as if it were larger than any actual value. That means the default behavior, with no NULLS FIRST/NULLS LAST specified, is: ascending order sorts NULLs to the end, and descending order sorts NULLs to the start. This is a dialect-specific default worth memorizing, since MySQL and SQL Server behave differently by default.

Sorting by expression or column position

You are not limited to sorting by a bare column name. Any expression computed from the row can be used, and as shorthand you can also refer to a column by its 1-based position in the select list.

Sort by a computed expression

SQL

SELECT name, price, price * 0.9 AS discounted_price
FROM products
ORDER BY price * 0.9 DESC;

Sort by column position (2nd column in the SELECT list)

SQL

SELECT name, price
FROM products
ORDER BY 2 DESC;

Tip

Sorting by position is convenient for quick, throwaway queries at the psql prompt, but it is fragile: if you later add or reorder columns in the SELECT list, the sort silently changes meaning. Prefer naming columns explicitly in application code and views.

Quick reference

Clause	Effect
`ORDER BY col`	Sort ascending (default)
`ORDER BY col ASC`	Sort ascending, explicit
`ORDER BY col DESC`	Sort descending
`ORDER BY a, b`	Sort by `a`, then `b` for ties
`ORDER BY col NULLS FIRST`	Force NULLs to the top
`ORDER BY col NULLS LAST`	Force NULLs to the bottom
`ORDER BY 2`	Sort by the 2nd selected column

ORDER BY runs after WHERE, GROUP BY, and HAVING, but before LIMIT/OFFSET
You can sort by columns that are not even in the SELECT list
Sorting on an indexed column can let PostgreSQL avoid an explicit sort step — see the indexes section later in this series