Sort Results with ORDER BY

You've built tables, loaded data, and filtered rows—but your result sets still feel like a shuffled deck. Without a guaranteed order, PostgreSQL returns rows in whatever physical order the planner chooses, which can change between queries. This lesson shows you how to take control and sort results with ORDER BY, so your reports, APIs, and dashboards always display data the way users expect.

The problem this lesson solves

Imagine you run a query like SELECT * FROM products; and the rows appear in a random-looking sequence. On one run, the cheapest product is at the top; on the next, an overdue inventory item. This unpredictability breaks pagination, messes up rankings, and makes debugging painful.

ORDER BY solves this by letting you specify exact sort rules—by column, by direction, or even by multiple columns. Without it, you literally have no guarantee of row order, because PostgreSQL may use a sequential scan (reading pages on disk) or a parallel worker that returns rows in a nondeterministic way. Need a top-10 leaderboard? Need the latest 5 orders for a customer? Need a stable pagination key? All of these demand ORDER BY.

Core concept / mental model

Think of ORDER BY as the director of a theater—once the actors (rows) leave the stage (the storage engine), the director arranges them in the exact line-up you request before the curtain rises (results are returned). You tell the director which property to base the line-up on (column), whether to go left-to-right (ascending) or right-to-left (descending), and what to do with ties (secondary sort keys).

Pro tip: The database does not store rows in a sorted order. ORDER BY is a processing step that happens after filtering (WHERE) and before returning results (or offsetting for pagination).

Why not trust default order?

Many beginners assume that SELECT * returns rows in insertion order. That is sometimes true for small tables, but as soon as you update, delete, or reindex, the physical order changes. Even the same query can return a different order depending on table size, indexes, or concurrent activity. Never rely on it—always use ORDER BY if order matters.

How it works step by step

1. Start with a SELECT statement that defines which columns and which rows to return.
2. Append ORDER BY at the end (after WHERE, GROUP BY, HAVING).
3. Specify one or more columns to sort by. For each, choose ASC (default) or DESC.
4. For ties, add a secondary column. The database sorts by the first key, then uses the second to break ties.
5. Optionally sort by expressions—e.g., ORDER BY price + tax or ORDER BY lower(name).
6. to NULLS FIRST or NULLS LAST to control where missing values go.

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

This returns products sorted from most expensive to least, and within the same price, alphabetically by name.

Sorting directions

• ASC — smallest/number first, alphabetically A→Z, dates oldest→newest.
• DESC — largest/number first, Z→A, dates newest→oldest.

Multiple columns

When you list columns with commas, the database processes them left to right—first key, then second key for ties, etc.

Null handling

By default, NULL sorts larger than any value in ascending order (so NULLs appear last in ASC, first in DESC). Use NULLS FIRST or NULLS LAST to override.

Hands-on walkthrough

Let's create a sample table and run a series of ORDER BY queries. Copy-paste these into psql or your favorite SQL client.

-- Create a demo table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    category TEXT,
    price NUMERIC(8,2),
    created_at DATE
);

-- Insert sample rows
INSERT INTO products (name, category, price, created_at) VALUES
('Laptop', 'Electronics', 999.99, '2024-01-15'),
('Mouse', 'Electronics', 29.99, '2024-03-10'),
('Desk', 'Furniture', 299.00, '2024-02-01'),
('Chair', 'Furniture', 149.50, '2024-04-22'),
('Monitor', 'Electronics', 450.00, '2024-05-03');

Now run the queries one by one:

-- 1) Sort by price ascending (cheapest first)
SELECT name, price FROM products ORDER BY price ASC;

-- 2) Sort by price descending (most expensive first)
SELECT name, price FROM products ORDER BY price DESC;

-- 3) Sort by category, then by name within category
SELECT category, name, price FROM products ORDER BY category ASC, name ASC;

-- 4) Sort by price descending, but put NULLs last (add a NULL later)
INSERT INTO products (name, category, price, created_at) VALUES ('Keyboard', 'Electronics', NULL, '2024-06-01');
SELECT name, price FROM products ORDER BY price DESC NULLS LAST;

Expected output (query 4):

    name     |  price  
-------------+---------
 Laptop      |  999.99
 Monitor     |  450.00
 Desk        |  299.00
 Chair       |  149.50
 Mouse       |   29.99
 Keyboard    |    NULL

Exercise: Flip the order

Run the same query but change DESC to ASC. Notice how NULL jumps to the top by default? Add NULLS LAST to keep it at the bottom—your report will look more polished.

Compare options / when to choose what

When to use multiple keys: Always use a secondary key when your primary key has duplicates (e.g., sorting by category alone will group products but within each category order is arbitrary). Adding name makes the output predictable.

When to sort by expression: If you need a sort rule based on a calculation (total cost, age in days), you can place that expression in ORDER BY without creating a new column. For complex expressions, consider creating a functional index.

Pro tip: For large tables, sorting by a column with an index can be much faster—PostgreSQL may use the index to return rows already in sorted order. We'll cover indexes in a later lesson.

Troubleshooting & edge cases

Error: column "x" must appear in the GROUP BY clause — If you use GROUP BY, your ORDER BY columns must be in the GROUP BY or in an aggregate function (like MAX(price)). Fix by adding the column to GROUP BY or wrapping in an aggregate.

Wrong order in text columns — PostgreSQL sorts text using the database collation. For case-insensitive sorting, use ORDER BY lower(name) or set a collation like C on the column.

NULLs appearing at unexpected spot — Remember the default behavior: NULLs are considered larger than any value in ASC, and smaller in DESC. If you see NULLs at the top when you expect them at the bottom, add NULLS LAST explicitly.

Sorting by a column not in the SELECT list — This is allowed in PostgreSQL (unless DISTINCT is used). It can be handy, but sometimes confuses readers—clarify with comments.

Performance problems — If your query is slow, check EXPLAIN to see if a sort step is on a large result set. Adding an index or filtering earlier (WHERE) can reduce the sort size dramatically.

What you learned & what's next

You've mastered the core of sorting results with ORDER BY: you know how to sort by one or multiple columns, control direction, handle NULLs, and sort by expressions. You also understand the importance of deterministic order for pagination and reporting.

Next up in the PostgreSQL Tutorial, you'll learn how to limit results with LIMIT and OFFSET — the perfect companion to ORDER BY for building paginated APIs and "top N" dashboards. Combined, these give you full control over which rows come back and in what order.

Pro tip: Always pair ORDER BY with LIMIT when you only need the top rows—otherwise you may sort the entire table unnecessarily.

Now try the practice recap below to test your new skills!

Practice recap

Create a small table of orders (customer, amount, order_date) and insert 10 rows. Write a query that returns the top 3 most recent orders per customer (use ORDER BY order_date DESC). Then add a second sort key to break ties by amount DESC. Check your output—do you see full names as expected? If you're comfortable, switch to NULLS LAST for a column that might have NULLs.