By the end of this lesson you can sort a result by several keys with different directions, place NULLs first or last on purpose, sort by expressions and aliases, and add tie-breakers so the order is deterministic.
Why it matters: Every report is read top to bottom: largest customers first, newest hires first, overdue items at the top. Without ORDER BY the database may return rows in any order, and a sort without a tie-breaker can change from one run to the next.
Dataset: Wadi Market online store. Examples run in the editor on the right.
A table has no built-in order. Without ORDER BY, PostgreSQL returns rows in whatever order is cheapest for it, which often looks like insertion order but can change after an update, with a different plan or on a bigger table. If the order matters, say so.
ORDER BY comes after WHERE and is the last clause PostgreSQL applies (before LIMIT, next lesson). Each sort key can be followed by ASC (ascending: smallest first, A–Z, oldest first, the default) or DESC (descending).
SELECT …
FROM …
WHERE …
ORDER BY key_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
key_2 [ASC | DESC] …;SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC;DESC puts the largest price at the top: the Falcon Book 16 Pro Laptop leads the list. Several products share a price, and nothing decides their order yet, which is why you'll add tie-breakers below.
Several sort keys
With more than one key, PostgreSQL sorts by the first; only rows with equal values in the first key are ordered by the second, and so on. Each key has its own direction: ORDER BY category_id ASC, unit_price DESC lists categories in ascending order and, inside each category, the most expensive product first.
A sort is deterministic only when no two rows can tie on all keys. If an exercise or a report asks for a specific order, end the list with a unique column (such as the id) as the final tie-breaker. Otherwise two runs of the same query may legitimately return tied rows in different orders, which breaks pagination and confuses anyone comparing reports.
SELECT category_id, product_name, unit_price
FROM products
ORDER BY category_id ASC, unit_price DESC, product_name;Rows are grouped by category_id (1, 2, 3 …); within each category the priciest product comes first, and product_name breaks any remaining price ties. Notice the first category in the list is 5: categories 1–4 are departments, which hold no products directly.
Where do NULLs go?
In PostgreSQL, NULL sorts as if it were larger than every value. So NULLs come last in ASC and first in DESC. That default is often wrong for a report: sorting products by cost DESC puts the products with unknown cost at the very top. Override it with NULLS FIRST or NULLS LAST after the direction.
SELECT product_name, unit_cost
FROM products
ORDER BY unit_cost DESC NULLS LAST, product_name;Without NULLS LAST, the Bakhoor Gift Box and the Kaftan Dress Eid Edition (cost not recorded) would head the list. With it they move to the bottom, ordered by name between themselves.
Sorting by expressions and aliases
ORDER BY runs after SELECT, so unlike WHERE it can use a column alias (ORDER BY unit_margin) as well as any expression (ORDER BY unit_price - unit_cost), and it can sort by a column that isn't displayed. You may also see ORDER BY 2 (the second column of the SELECT list); avoid it, because the meaning changes silently when someone edits the SELECT list.
Text sorts character by character. In this course's database, uppercase letters sort before lowercase, and a leading space sorts before letters, so messy values like ' Khobar' can surprise you at the top of a sorted list.
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date >= '2025-10-01'
ORDER BY hire_date DESC, last_name, first_name, employee_id;Most recent hire first. People hired on the same day are listed alphabetically by family name, then given name, and employee_id guarantees a fixed order even if two people share a full name.