5 ORDER BY

ORDER BY sorts the result set. Without it, the order of rows is undefined — PostgreSQL is free to return rows in whatever sequence the planner finds convenient, and that order can change between runs. If your application cares about order, say so explicitly.

What you’ll learn

• Sorting with ORDER BY column
• ASC (default) and DESC
• Multi-column sort (tie-breakers)
• Controlling where NULLs land (NULLS FIRST / NULLS LAST)
• Sorting by expressions and by position

Example

-- Default ascending
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename
LIMIT 4;

-- Descending
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename DESC
LIMIT 4;

-- Multi-column: sort by schema, then by name within schema
SELECT schemaname, tablename
FROM pg_tables
ORDER BY schemaname, tablename
LIMIT 5;

-- NULLs FIRST / LAST: default for ASC is NULLS LAST, for DESC is NULLS FIRST
SELECT * FROM (VALUES (1), (NULL), (2)) AS t(n) ORDER BY n NULLS FIRST;

-- Sort by an expression
SELECT tablename, length(tablename) AS len
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY length(tablename) DESC
LIMIT 3;

ORDER BY tablename sorts in ascending order — ASC is the default. DESC reverses. When two rows have the same first sort key, the second is used as a tie-breaker, and so on. You can sort by any expression, including ones that don’t appear in the select list, and by output position (ORDER BY 1, 2) — though using column names is much more readable.

NULL handling is worth knowing: ASC puts NULLs last; DESC puts NULLs first. Override with NULLS FIRST or NULLS LAST. For applications that always want to see NULLs at the end regardless of direction, write it out — relying on the default is a source of bugs when someone later flips ASC to DESC.

Run the example

To run:

$ psql -f source/order-by.sql postgres
 tablename
--------------
 pg_aggregate
 pg_am
 pg_amop
 pg_amproc
(4 rows)

   tablename
-----------------
 pg_user_mapping
 pg_type
 pg_ts_template
 pg_ts_parser
(4 rows)
 ...

Common pitfalls

• Sorting on text and getting surprising results: text sort respects the database’s collation (e.g. en_US.UTF-8), which may sort case-insensitively or treat accents specially. Use ORDER BY col COLLATE "C" for byte-order sorting.
• Forgetting ORDER BY with LIMIT: LIMIT 10 without ORDER BY returns some 10 rows, not necessarily the first or smallest. Pagination is meaningless without a deterministic order.
• Two rows with the same primary sort key may appear in any order — add the primary key as a final tie-breaker for fully deterministic results (ORDER BY created_at DESC, id DESC).
• Sorting can be expensive on large result sets. PostgreSQL can use an index to avoid sorting if the index order matches the requested order.

Downloads

SQL file: order-by.sql · All lesson files · Setup guide