ORDER BY

Introduction to PostgreSQL ORDER BY

When you run a SELECT query, PostgreSQL does not guarantee any particular row order unless you specify one. The ORDER BY clause controls how the result set is sorted before it is returned to the client.

The basic syntax is:

SELECT select_list
FROM table_name
ORDER BY sort_expression1 [ASC | DESC],
         sort_expression2 [ASC | DESC],
         ...;

ASC (ascending) is the default direction — you can omit it if ascending order is what you want. DESC reverses the sort to descending.

ORDER BY examples

These examples use the customer table from the standard DVD rental sample database.

Sort by one column ascending:

SELECT first_name, last_name
FROM customer
ORDER BY first_name ASC;

Because ASC is the default, you can also write it as:

SELECT first_name, last_name
FROM customer
ORDER BY first_name;

Sort by one column descending:

SELECT first_name, last_name
FROM customer
ORDER BY last_name DESC;

Sort by multiple columns:

PostgreSQL applies the sort expressions from left to right. Rows are first sorted by first_name ascending, then within rows that share the same first name, by last_name descending:

SELECT first_name, last_name
FROM customer
ORDER BY first_name ASC, last_name DESC;

Sort by an expression or alias:

Because ORDER BY is evaluated after SELECT, you can reference column aliases defined in the select list:

SELECT first_name, LENGTH(first_name) AS name_len
FROM customer
ORDER BY name_len DESC;

This returns customers sorted from longest to shortest first name.

Handling NULL values in ORDER BY

NULL represents unknown data. By default, PostgreSQL places NULLs last when sorting ascending and first when sorting descending. You can override this with NULLS FIRST or NULLS LAST:

-- Ascending, NULLs last (the default)
SELECT num FROM sort_demo ORDER BY num ASC NULLS LAST;

-- Ascending, NULLs first (override)
SELECT num FROM sort_demo ORDER BY num ASC NULLS FIRST;

-- Descending, NULLs last (override)
SELECT num FROM sort_demo ORDER BY num DESC NULLS LAST;

Example output for ascending with NULLS LAST:

num
-----
  1
  2
  3
null
(4 rows)

Tips for using ORDER BY effectively

• Always include ORDER BY when using LIMIT or OFFSET — without it, the rows you receive are unpredictable because PostgreSQL may return them in any order.
• You can sort by a column that is not in the select list, as long as it is accessible from the FROM clause.
• Sorting large result sets can be expensive. An index on the sort column lets PostgreSQL avoid a full sort by scanning the index in order.
• For pagination, combine ORDER BY with LIMIT and OFFSET, or use keyset pagination (comparing against the last seen value) for better performance on large offsets.

Reference: PostgreSQL documentation — SELECT / ORDER BY.