ORDER BY — Sorting Results¶

Raw query results come back in no guaranteed order. Without ORDER BY, the database returns rows in whatever order is most convenient for the storage engine — which changes as rows are inserted and deleted. ORDER BY controls exactly how data is sorted — by date, by value, alphabetically, or by multiple columns at once. Every report depends on meaningful sort order.

Learning Objectives¶

Sort results ascending and descending
Sort by multiple columns with independent directions
Sort by calculated expressions and aliases
Understand how NULL values sort differently across databases and how to control them

Basic Sorting¶

-- Sort orders from cheapest to most expensive (default: ascending)
SELECT order_id, customer_id, order_date, quantity * unit_price AS order_total
FROM orders
ORDER BY order_total;

Default is ascending (ASC) — smallest to largest, A to Z, oldest to newest.

Sample output:

order_id	customer_id	order_date	order_total
O004	C001	2024-01-10	35.99
O003	C002	2024-01-09	149.97
O006	C005	2024-01-14	152.98
O007	C003	2024-01-15	249.95

No ORDER BY = no guaranteed order

Without ORDER BY, SQL does not guarantee the order of rows. In practice, PostgreSQL and MySQL often return rows in insertion order or index order — but this is an implementation detail that can change after an index rebuild, a vacuum, or a query planner update. Never rely on implicit ordering in production queries.

Ascending and Descending¶

-- Largest orders first (most relevant for "top N" reports)
SELECT order_id, customer_id, quantity * unit_price AS order_total
FROM orders
WHERE status = 'completed'
ORDER BY order_total DESC;

Sample output:

order_id	customer_id	order_total
O002	C003	389.00
O001	C001	298.00
O007	C003	249.95

-- Most recent orders first — standard for operational dashboards
SELECT order_id, order_date, status
FROM orders
ORDER BY order_date DESC;

Sorting by Multiple Columns¶

SQL applies sort columns left to right. The second column only breaks ties from the first.

-- Sort by status A–Z, then within each status by order value highest first
SELECT order_id, status, quantity * unit_price AS order_total
FROM orders
ORDER BY status ASC, order_total DESC;

Sample output:

order_id	status	order_total
O002	completed	389.00
O001	completed	298.00
O006	completed	152.98
O008	pending	389.00
O007	pending	249.95
O005	cancelled	149.00

For mid-level analysts

Each column in ORDER BY has its own ASC/DESC direction. ORDER BY status ASC, order_total DESC is valid — the direction from the first column does not carry over to the second. Forgetting to specify direction for each column is a common source of subtle sort errors.

Sorting by Expressions and Aliases¶

You can sort by a calculated expression. In most databases you can also use a SELECT alias in ORDER BY (unlike WHERE, which runs before SELECT):

SELECT
    order_id,
    customer_id,
    quantity * unit_price   AS order_total
FROM orders
ORDER BY order_total DESC;   -- alias works here because ORDER BY runs after SELECT

-- Sort by month extracted from date (MySQL)
SELECT order_id, order_date, quantity * unit_price AS order_total
FROM orders
ORDER BY MONTH(order_date), order_total DESC;

-- PostgreSQL equivalent
SELECT order_id, order_date, quantity * unit_price AS order_total
FROM orders
ORDER BY EXTRACT(MONTH FROM order_date), order_total DESC;

Sample output:

order_id	order_date	order_total
O002	2024-01-07	389.00
O001	2024-01-05	298.00
O007	2024-01-15	249.95

Sorting by Column Position¶

You can reference columns by their position number in the SELECT list:

-- Sort by the 3rd column (total_revenue) descending
SELECT
    customer_id,
    COUNT(*)                    AS total_orders,
    SUM(quantity * unit_price)  AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY 3 DESC;   -- 3 refers to total_revenue

Column position sorting breaks silently

If someone inserts a column into the SELECT list, positions shift and the sort changes without any error. ORDER BY 3 DESC now sorts by a different column. Use column names or aliases in production code — positional sorting is only acceptable in quick ad-hoc queries.

NULL Values in Sorting¶

NULL sort order differs between databases and often surprises analysts:

Database	`ORDER BY col ASC`	`ORDER BY col DESC`
PostgreSQL	NULLs sort last	NULLs sort first
MySQL	NULLs sort first	NULLs sort last
SQL Server	NULLs sort first	NULLs sort last

Control it explicitly rather than relying on database defaults:

-- PostgreSQL — NULLs always at the end regardless of direction
ORDER BY delivery_date ASC  NULLS LAST;
ORDER BY delivery_date DESC NULLS LAST;

-- MySQL — ISNULL() workaround (ISNULL returns 1 for NULL, 0 otherwise)
-- Sort non-NULLs first (ascending), then delivery_date ascending
ORDER BY ISNULL(delivery_date) ASC, delivery_date ASC;

-- MySQL — NULLs last for descending sort
ORDER BY ISNULL(delivery_date) ASC, delivery_date DESC;

Test NULL sort behaviour explicitly

Never assume NULLs will sort where you expect. Build a test case with a few NULL rows and verify the output. This is especially important when building ranked dashboards, leaderboards, or reports where undelivered/unknown values have a meaningful position.

Practical Example — Top 5 Customers by Revenue¶

SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name)  AS full_name,
    COUNT(o.order_id)                        AS total_orders,
    SUM(o.quantity * o.unit_price)           AS total_revenue,
    AVG(o.quantity * o.unit_price)           AS avg_order_value
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_revenue DESC
LIMIT 5;

Sample output:

customer_id	full_name	total_orders	total_revenue	avg_order_value
C003	Sarah Johnson	12	4195.50	349.63
C001	Priya Sharma	9	2189.25	243.25
C002	Arjun Mehta	6	1289.00	214.83

Practice Exercises¶

Warm-up 1. Sort all products by unit_price from most expensive to least expensive. 2. Sort customers alphabetically by last_name, then by first_name within the same last name. 3. Sort orders by order_date from most recent to oldest.

Main 4. Find the 10 most recent orders — include order_id, customer_id, order_date, and gross total (quantity * unit_price). 5. Sort orders by status A–Z, and within each status by gross total highest first. Show order_id, status, and gross_total. 6. Sort customers by country ascending, then city ascending, then last_name ascending.

Stretch 7. Write a query that returns each customer's most recent order date, sorted by most recent first. (Hint: GROUP BY customer_id + MAX(order_date), then ORDER BY.) 8. A products table has a nullable discount_pct column. Sort products by price after discount (unit_price * (1 - discount_pct / 100)), treating NULL discounts as 0%. Put the most discounted items (lowest final price) first.

Interview Questions¶

[Beginner] What is the default sort order in ORDER BY?

Show answer

The default is ASC (ascending) — smallest to largest for numbers, A to Z for strings, oldest to newest for dates. If you omit ASC or DESC, ascending is assumed.

ORDER BY order_total         -- ascending (cheapest first)
ORDER BY order_total ASC     -- same as above, explicit
ORDER BY order_total DESC    -- descending (most expensive first)

Most "top N" queries want DESC — largest revenue, most recent date, highest count.

[Beginner] How do you sort by multiple columns, with different directions for each?

Show answer

List the columns separated by commas in ORDER BY. Specify ASC or DESC for each one independently:

SELECT order_id, status, quantity * unit_price AS order_total
FROM orders
ORDER BY status ASC, order_total DESC;

This sorts by status alphabetically (A to Z), and within each status group, by order_total from highest to lowest. The direction from one column does not carry to the next — each needs its own ASC/DESC.

[Mid-level] You need to sort by last name, with NULLs at the bottom regardless of sort direction. How do you do this in PostgreSQL? In MySQL?

Show answer

PostgreSQL — use NULLS LAST / NULLS FIRST modifier:

-- Ascending, NULLs at the bottom
ORDER BY last_name ASC NULLS LAST;

-- Descending, NULLs still at the bottom
ORDER BY last_name DESC NULLS LAST;

MySQL — no NULLS LAST syntax, use a workaround:

-- ISNULL() returns 1 for NULL, 0 for non-NULL
-- Sorting ISNULL() ASC puts non-NULLs (0) before NULLs (1)
ORDER BY ISNULL(last_name) ASC, last_name ASC;   -- ascending with NULLs last
ORDER BY ISNULL(last_name) ASC, last_name DESC;  -- descending with NULLs last

Always test explicitly — NULL sort defaults differ by database and can silently produce wrong results in reports.

[Mid-level] Can you use a column alias in ORDER BY? In WHERE? Explain why the answer differs.

Show answer

In ORDER BY: Yes, aliases work. ORDER BY runs at step 6 in the logical execution order, after SELECT (step 5), so the alias is defined by the time it's needed.
In WHERE: No, not in standard SQL or PostgreSQL. WHERE runs at step 2, before SELECT (step 5), so the alias doesn't exist yet.

SELECT quantity * unit_price AS order_total
FROM orders
WHERE order_total > 100        -- ERROR in PostgreSQL / may silently fail in MySQL
ORDER BY order_total DESC;     -- WORKS — ORDER BY runs after SELECT

Fix: repeat the expression in WHERE:

WHERE quantity * unit_price > 100
ORDER BY order_total DESC;

MySQL sometimes allows alias references in HAVING but not WHERE. PostgreSQL is strict about both. Write expressions in WHERE, use aliases only in ORDER BY.

[Senior] A query with ORDER BY on a 100-million-row table takes 45 seconds. What would you investigate, and what are your options to improve it?

Show answer

Step 1: Run EXPLAIN / EXPLAIN ANALYZE

Check whether a sort is actually happening in memory or on disk (filesort in MySQL, external merge sort in PostgreSQL). A sort that spills to disk is dramatically slower.

Common causes and fixes:

No index on the ORDER BY column: Add an index on the sort column. The database can use the index to return rows in order without a full sort. For ORDER BY order_date DESC, add: CREATE INDEX idx_orders_date ON orders(order_date DESC);
Combined filter + sort: If you have WHERE status = 'completed' ORDER BY order_date DESC, a composite index on (status, order_date) can satisfy both the filter and the sort in one index scan.
High cardinality sort with LIMIT: If you only need LIMIT 10, the database can stop the sort early — this is called a "top-N" sort and is much faster than a full sort. Ensure the query planner sees the LIMIT.
Work memory settings: In PostgreSQL, increasing work_mem allows the sort to happen in memory instead of spilling to disk: SET work_mem = '256MB'; for the session.
Materialised result: If the same sort is requested repeatedly with no data changes, pre-compute and store the sorted result in a summary table, updated by a scheduled job.

Previous: 02-where-clause | Next: 04-limit-and-distinct