ORDER BY and LIMIT

SQL Result Sets Are Unordered by Default

A SQL table has no inherent order. When you run SELECT * FROM orders, the database returns rows in whatever order is most efficient for it — typically the order they appear on disk, but that is an implementation detail, not a guarantee. The database is free to return rows in any order unless you explicitly request a specific order with ORDER BY.

This is not a limitation. It is a design choice. By not guaranteeing order, the database is free to choose the fastest execution plan. When you add ORDER BY, you are telling the database: "I am willing to pay a sorting cost because the order matters for this result."

When Order Matters

Order matters whenever a human reads the result or when the result feeds a process that assumes a sequence:

• Top-N reports: "Show me the 10 highest-revenue customers." Without ORDER BY, you get 10 arbitrary customers.
• Time-series display: A chart of daily revenue is meaningless if the days are not in chronological order.
• Pagination: Page 2 of a sorted list must continue where page 1 ended. Without a deterministic order, rows can appear on multiple pages or be skipped.
• Tie-breaking in competitions: When two entries have the same score, the tiebreaker rule must be explicit.

When Order Does Not Matter

Order does not matter for aggregate queries that return a single row (SELECT COUNT(*) FROM orders), for queries whose results are consumed by code that sorts internally, or for quick data exploration. Adding ORDER BY to these queries wastes time and resources.

The Cost of Sorting

Sorting is not free. The database must materialize the full result set, sort it in memory or on disk, and then return it. On large tables, this can be expensive. The cost grows with the number of rows and the number of sort keys. This is why you should filter with WHERE before sorting — sort the smallest possible set.

Basic Syntax

SELECT column_list
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

ORDER BY is the last clause in the query (before LIMIT, which comes after it). It accepts one or more columns, each with an optional direction.

-- Sort by order_date, oldest first
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date;

-- Sort by total_amount, largest first
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY total_amount DESC;

-- Sort by region (A-Z), then by total_amount (largest first) within each region
SELECT order_id, region, total_amount
FROM orders
ORDER BY region ASC, total_amount DESC;

ASC and DESC

ASC (ascending) is the default. DESC (descending) reverses the order. Each sort key has its own direction.

-- region A-Z, but within each region, highest amount first
ORDER BY region ASC, total_amount DESC;

This is the most common pattern in analytical reports: group by a category (ascending for readability) and rank by a measure (descending so the best appears first).

Sorting by Column Position

ORDER BY can reference columns by their position in the SELECT list:

SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC; -- sorts by salary

This works but is fragile. If someone reorders the SELECT list, the sort changes silently. Always sort by column name in production code.

Sorting by Expression

ORDER BY can use any expression, including computed ones:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary * 12 DESC;

This is useful when the sort key is derived. You can also sort by a column alias defined in SELECT, because ORDER BY executes after SELECT.

SELECT first_name, last_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

NULLs in Sorting

NULL values sort differently depending on the database. In PostgreSQL, NULLs sort last in ASC order and first in DESC order. In MySQL and SQL Server, NULLs sort first in ASC order and last in DESC order. This inconsistency means you should not rely on default NULL placement — use NULLS LAST or NULLS FIRST explicitly (supported in PostgreSQL, Oracle, and SQLite 3.30+).

ORDER BY total_amount DESC NULLS LAST;

For databases that do not support NULLS LAST, use a CASE expression:

ORDER BY CASE WHEN total_amount IS NULL THEN 1 ELSE 0 END, total_amount DESC;

This pushes NULLs to the end regardless of the database's default behavior.

The Problem of Non-Deterministic Order

When two rows have the same value in the sort column, their relative order is undefined. The database may return them in any order, and that order can change between executions.

-- Two orders with total_amount = 250.00
SELECT order_id, total_amount
FROM orders
ORDER BY total_amount DESC;

If orders 1 and 9 both have total_amount 250.00, the database might return order 1 first on one run and order 9 first on the next. For a report, this is a bug. For pagination, it is a data integrity problem — a row can appear on two pages or none.

The Tiebreaker Solution

Add a unique column as the final sort key. This makes the order fully deterministic — no two rows can have the same combination of sort keys.

SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY total_amount DESC, order_id ASC;

Now if two orders have the same total_amount, the one with the lower order_id appears first. The order is stable across executions.

Choosing a Tiebreaker

The tiebreaker should be:

1. Unique: No two rows can have the same value. A primary key is ideal.
2. Stable: The value does not change over time. An order_id is stable; a customer's current balance is not.
3. Meaningful when possible: If the report is about customers, tiebreak by customer_name before customer_id. The name is more readable in a debug session.

-- Good: unique and stable
ORDER BY total_amount DESC, order_id ASC;

-- Bad: not unique (two orders can share a customer_id)
ORDER BY total_amount DESC, customer_id ASC;

Deterministic Pagination

Pagination requires deterministic order. The standard pattern:

-- Page 1
SELECT order_id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, order_id ASC
LIMIT 20 OFFSET 0;

-- Page 2
SELECT order_id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, order_id ASC
LIMIT 20 OFFSET 20;

The ORDER BY clause must be identical on every page. The OFFSET increases by the page size. Without the tiebreaker (order_id), a row could appear on both pages if its order_date matches a row on the previous page.

When You Do Not Need a Tiebreaker

If the result is consumed by code that does not care about order (e.g., loading into a set or a hash map), a tiebreaker is unnecessary overhead. If the result is for a human to read, always add one. The cost is negligible; the benefit is reproducibility.

The LIMIT Clause

LIMIT caps the number of rows returned. It is applied after sorting, so it returns the first N rows in the sorted order.

-- Top 5 orders by revenue
SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY total_amount DESC
LIMIT 5;

Without ORDER BY, LIMIT returns an arbitrary set of 5 rows. With ORDER BY, it returns the 5 rows that sort first. The combination of ORDER BY and LIMIT is the foundation of top-N reporting.

LIMIT with OFFSET

OFFSET skips the first N rows before applying LIMIT. It is used for pagination.

-- Skip 10 rows, return the next 5
SELECT order_id, total_amount
FROM orders
ORDER BY total_amount DESC, order_id ASC
LIMIT 5 OFFSET 10;

The pattern for page-based pagination:

LIMIT page_size OFFSET (page_number - 1) * page_size;

OFFSET 0 is the default, so LIMIT 5 and LIMIT 5 OFFSET 0 are equivalent.

The Performance Problem with Large OFFSETs

OFFSET does not skip rows efficiently. The database must read and discard all skipped rows before returning the requested ones. LIMIT 20 OFFSET 1000000 reads 1,000,020 rows to return 20. On large tables, this is prohibitively slow.

The alternative is keyset pagination (also called seek pagination), which remembers the last row of the previous page and filters from there:

-- Instead of OFFSET, use the last seen values
SELECT order_id, order_date, total_amount
FROM orders
WHERE (order_date, order_id) < ('2024-03-15', 42)
ORDER BY order_date DESC, order_id ASC
LIMIT 20;

This uses an index on (order_date, order_id) to jump directly to the starting point. It is dramatically faster for deep pagination. The tradeoff is complexity — you must track the last seen values and handle the tuple comparison correctly.

Top-N Per Group

A common analytical question: "What are the top 3 products by revenue in each category?" This requires a window function (ROW_NUMBER or RANK), which is covered in the aggregation topics. The pattern:

SELECT category, product_name, revenue
FROM (
    SELECT
        category,
        product_name,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3;

The inner query assigns a rank within each category. The outer query filters to the top 3 per category. This is the standard top-N-per-group pattern.

LIMIT vs TOP vs FETCH FIRST

Different databases use different syntax for limiting rows:

• MySQL, PostgreSQL, SQLite: LIMIT n
• SQL Server: SELECT TOP (n) ...
• Oracle 12c+: FETCH FIRST n ROWS ONLY
• Standard SQL: FETCH FIRST n ROWS ONLY

The logic is identical. Only the syntax differs. When writing portable queries, use the standard FETCH FIRST form where supported.

The Full Pattern

A production top-N report combines filtering, sorting, and limiting:

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue
FROM orders
WHERE status = 'completed'
  AND order_date >= '2024-01-01'
GROUP BY customer_id
ORDER BY total_revenue DESC, customer_id ASC
LIMIT 10;

This query answers: "Who are the top 10 customers by completed revenue in 2024?" Each clause plays a role:

1. WHERE filters to completed orders in 2024.

2. GROUP BY aggregates per customer.

3. ORDER BY sorts by revenue descending, with customer_id as tiebreaker.

4. LIMIT returns only the top 10.

Why the Tiebreaker Matters Here

If two customers have the same total_revenue, the tiebreaker (customer_id) ensures the same customer always appears in the same position. Without it, a customer might appear in the top 10 on one day and fall to 11th on the next run because the database returned tied rows in a different order.

Common Top-N Report Types

1. Top N by measure: ORDER BY revenue DESC LIMIT 10
2. Bottom N by measure: ORDER BY revenue ASC LIMIT 10
3. Top N by recency: ORDER BY order_date DESC LIMIT 10
4. Top N per group: Window function with PARTITION BY
5. Top N with minimum threshold: Add HAVING SUM(total_amount) > 1000 before ORDER BY

Verifying Your Top-N Query

Before trusting a top-N result, verify:

1. The filter is correct: Does WHERE include exactly the rows you want?
2. The sort direction is correct: DESC for largest first, ASC for smallest first.
3. The tiebreaker is present: Is the final sort key unique?
4. The limit is applied after sorting: LIMIT is always last (before OFFSET if present).
5. The result is deterministic: Run the query twice. The results should be identical.

What You Should Remember

ORDER BY and LIMIT are the final steps in query construction. They shape the output for the consumer. Get them right by always specifying a sort direction, always adding a unique tiebreaker, and always applying LIMIT after ORDER BY. The cost of these practices is negligible; the cost of missing them is incorrect reports and unreproducible results.