LIMIT

Definition

LIMIT caps the number of rows returned by a query, regardless of how many rows actually matched.

sql
SELECT column1 FROM table_name
ORDER BY ...
LIMIT n;

(In SQL Server the equivalent is SELECT TOP n, and standard SQL also offers FETCH FIRST n ROWS ONLY — but LIMIT is the common syntax in PostgreSQL, MySQL, and SQLite.)

Worked Example

sql
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Result (top 3 most expensive):

 name               | price
--------------------+---------
 Bluetooth Speaker  | 1499.00
 Table Lamp         |  699.00
 Desk Lamp          |  899.00

(Ordered by price DESC then capped at 3 rows: Bluetooth Speaker 1499, Desk Lamp 899, Table Lamp 699 — showing the actual top 3 highest prices.)

The Critical Pitfall: LIMIT Without ORDER BY

sql
SELECT name, price FROM products LIMIT 3;   -- DANGEROUS

This returns some 3 rows — but which 3 is entirely up to the engine's internal execution plan (physical storage order, index scan order, parallel worker scheduling, etc.). There is no guarantee it is the same 3 rows every time you run it, especially as the table grows, gets updated, is vacuumed/reorganized, or the query planner picks a different plan. This is one of the most common real-world SQL bugs: code that "seems to always return the newest rows" in dev, then breaks in production once the table is large enough for the planner to choose a different access path.

Rule: `LIMIT` only produces a deterministic, meaningful result when paired with `ORDER BY`.

sql
-- Meaningful: deterministic top-3 cheapest
SELECT name, price FROM products ORDER BY price ASC LIMIT 3;

-- Meaningless: "some" 3 rows, order and identity not guaranteed
SELECT name, price FROM products LIMIT 3;

LIMIT with Zero or Large Values

• LIMIT 0 returns zero rows (sometimes used to test query validity or fetch only column metadata).
• LIMIT larger than the number of matching rows simply returns all matching rows — no error.

sql
SELECT name FROM products ORDER BY price LIMIT 1000;  -- returns all 8 rows, no error

Key Takeaways

• *Q: What happens if you run `SELECT FROM products LIMIT 5` twice without ORDER BY — are the two result sets guaranteed identical?**

A: No. Without ORDER BY, the engine is free to change which rows it returns and in what order.

• Q: Does LIMIT error if fewer rows exist than requested?

A: No, it just returns however many rows are available.

• Q: What is the fix for non-deterministic LIMIT results?

A: Always add an ORDER BY clause — ideally one that ends in a unique column so ties are also broken deterministically (see Stable Sorting, 13.9).