SQL Limit

The LIMIT clause restricts how many rows a query returns, no matter how many rows actually match your WHERE and JOIN conditions. It’s the clause behind every “top 10” list, every paginated table of results, and every “show me one example row” query you’ll ever write. Used well, it also makes queries faster by letting the database stop working as soon as it has enough rows.

Overview / How LIMIT Works

LIMIT is applied at the very end of query processing, after the database has already figured out which rows match your filters, grouped and aggregated them if needed, and sorted them. Conceptually, the engine builds the full, correctly ordered result set first, and only then keeps the first N rows and throws the rest away. In practice, a good query planner is smarter than that: if you sort by a column that has an index, the engine can walk the index in order and stop as soon as it has collected enough rows, without ever touching (or sorting) the rest of the table. This is why LIMIT combined with a well-chosen ORDER BY and a supporting index can be dramatically faster than fetching everything and trimming it in application code.

LIMIT is not part of the ANSI SQL standard’s original syntax, but it is supported by SQLite, MySQL, PostgreSQL, and many others. Other engines spell the same idea differently: SQL Server and older Sybase use SELECT TOP n, and the ANSI SQL:2008 standard (supported by PostgreSQL, Oracle 12c+, SQL Server 2012+, and DB2) uses OFFSET n ROWS FETCH FIRST m ROWS ONLY. Oracle also has the older ROWNUM pseudo-column trick. The concept is universal even though the keyword isn’t.

Syntax

SELECT column_list
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT number_of_rows
OFFSET number_to_skip;

Clause	Required?	Purpose
SELECT ... FROM ...	Yes	Defines which columns and table(s) to read from.
WHERE	No	Filters rows before sorting or limiting; reduces the candidate set.
ORDER BY	Strongly recommended	Determines which rows count as “first” — without it, LIMIT’s choice of rows is not guaranteed.
LIMIT number_of_rows	Yes (to use LIMIT)	Caps the result set to at most this many rows.
OFFSET number_to_skip	No	Skips this many rows before starting to return results; used for pagination.

Dialect	Equivalent syntax
SQLite / MySQL / PostgreSQL	LIMIT n / LIMIT n OFFSET m
SQL Server	SELECT TOP n ... or OFFSET m ROWS FETCH NEXT n ROWS ONLY
Oracle (12c+)	OFFSET m ROWS FETCH FIRST n ROWS ONLY
Oracle (legacy)	WHERE ROWNUM <= n

Examples

Example 1: The N highest-paid employees

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL,
    hire_date TEXT
);

INSERT INTO employees (id, name, department, salary, hire_date) VALUES
(1, 'Alice Johnson', 'Engineering', 95000, '2020-03-15'),
(2, 'Bob Smith', 'Sales', 72000, '2019-07-01'),
(3, 'Carla Diaz', 'Marketing', 68000, '2021-01-10'),
(4, 'David Lee', 'Engineering', 105000, '2018-11-20'),
(5, 'Ella Brown', 'Sales', 80000, '2022-05-05'),
(6, 'Frank Wu', 'Engineering', 60000, '2023-02-18');

SELECT name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Result:

name	department	salary
David Lee	Engineering	105000
Alice Johnson	Engineering	95000
Ella Brown	Sales	80000

The engine sorts all six employees by salary descending, then keeps only the first three rows. Without the ORDER BY, LIMIT 3 would still return exactly three rows — just not necessarily the highest-paid ones.

Example 2: Pagination with OFFSET

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL,
    hire_date TEXT
);

INSERT INTO employees (id, name, department, salary, hire_date) VALUES
(1, 'Alice Johnson', 'Engineering', 95000, '2020-03-15'),
(2, 'Bob Smith', 'Sales', 72000, '2019-07-01'),
(3, 'Carla Diaz', 'Marketing', 68000, '2021-01-10'),
(4, 'David Lee', 'Engineering', 105000, '2018-11-20'),
(5, 'Ella Brown', 'Sales', 80000, '2022-05-05'),
(6, 'Frank Wu', 'Engineering', 60000, '2023-02-18');

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2 OFFSET 2;

Result:

name	salary
Ella Brown	80000
Bob Smith	72000

Sorted descending by salary, the full order is David (105000), Alice (95000), Ella (80000), Bob (72000), Carla (68000), Frank (60000). OFFSET 2 skips the first two rows (David, Alice), and LIMIT 2 then returns the next two — this is exactly how “page 2” of a results table is built.

Example 3: LIMIT combined with WHERE

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL,
    hire_date TEXT
);

INSERT INTO employees (id, name, department, salary, hire_date) VALUES
(1, 'Alice Johnson', 'Engineering', 95000, '2020-03-15'),
(2, 'Bob Smith', 'Sales', 72000, '2019-07-01'),
(3, 'Carla Diaz', 'Marketing', 68000, '2021-01-10'),
(4, 'David Lee', 'Engineering', 105000, '2018-11-20'),
(5, 'Ella Brown', 'Sales', 80000, '2022-05-05'),
(6, 'Frank Wu', 'Engineering', 60000, '2023-02-18');

SELECT name, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 2;

Result:

name	salary
David Lee	105000
Alice Johnson	95000

WHERE narrows the candidate rows to the three Engineering employees first, then ORDER BY sorts just those, and LIMIT 2 keeps the top two. Frank Wu (60000) is excluded because he ranks third within the filtered set.

Example 4: LIMIT after GROUP BY

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL,
    hire_date TEXT
);

INSERT INTO employees (id, name, department, salary, hire_date) VALUES
(1, 'Alice Johnson', 'Engineering', 95000, '2020-03-15'),
(2, 'Bob Smith', 'Sales', 72000, '2019-07-01'),
(3, 'Carla Diaz', 'Marketing', 68000, '2021-01-10'),
(4, 'David Lee', 'Engineering', 105000, '2018-11-20'),
(5, 'Ella Brown', 'Sales', 80000, '2022-05-05'),
(6, 'Frank Wu', 'Engineering', 60000, '2023-02-18');

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;

Result:

department	avg_salary
Engineering	86666.666666667

Here GROUP BY collapses the six rows into three department averages (Engineering ≈ 86666.67, Sales = 76000, Marketing = 68000), ORDER BY ranks the departments by average salary, and LIMIT 1 keeps only the single highest-paying department. This shows that LIMIT works on whatever the query has produced so far — raw rows or aggregated groups.

How It Works Step by Step (Under the Hood)

Every query with WHERE, GROUP BY, ORDER BY, and LIMIT is logically processed in this order, regardless of the order you type the clauses in:

• FROM / JOIN — identify the source table(s) and combine rows via joins.
• WHERE — filter individual rows before any grouping happens.
• GROUP BY — collapse rows into groups, if present.
• HAVING — filter those groups.
• SELECT — compute the final output columns and expressions.
• ORDER BY — sort the resulting rows.
• LIMIT / OFFSET — keep only a slice of the sorted output, discarding the rest.

LIMIT is always the last thing to run. That’s exactly why it must come after ORDER BY in your SQL text — it operates on already-sorted output. It’s also why LIMIT can never “limit” what a WHERE or HAVING clause sees; those clauses run on the full candidate set regardless of how small your final LIMIT is.

Common Mistakes

Mistake 1: Putting LIMIT before ORDER BY

SELECT name, salary
FROM employees
LIMIT 3
ORDER BY salary DESC;

This fails outright — SQL requires ORDER BY to appear before LIMIT in the statement, matching the logical processing order. The fix is simply to reorder the clauses:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Mistake 2: Using LIMIT/OFFSET without a deterministic ORDER BY

SELECT name, salary
FROM employees
LIMIT 2
OFFSET 2;

This runs without error, but it’s silently unreliable. With no ORDER BY, the database is free to return rows in any order it finds convenient (often insertion order in practice, but never guaranteed) — so “page 2” of your results can change between runs, after an update, or after a version upgrade of the database engine. Always give LIMIT/OFFSET a fully deterministic sort to work from, adding a unique tiebreaker column if the primary sort key can repeat:

SELECT name, salary
FROM employees
ORDER BY id
LIMIT 2
OFFSET 2;

Best Practices

• Always pair LIMIT with an explicit ORDER BY — otherwise “top N” or “page N” has no defined meaning.
• When rows can tie on your sort column, add a unique column (like an id) as a secondary sort key so results are stable and reproducible.
• Put an index on the column(s) used in ORDER BY when the table is large; it lets the engine fetch the top rows without sorting the whole table.
• Be aware that large OFFSET values are slow on big tables — the engine typically still has to walk past every skipped row. For deep pagination, prefer “keyset pagination” (a WHERE id > last_seen_id filter) over ever-growing OFFSET values.
• Remember LIMIT is applied after GROUP BY/HAVING/aggregation, so it limits result rows or groups, never the rows an aggregate function sees.
• If you need portable code across databases, know your target’s dialect: LIMIT/OFFSET (SQLite, MySQL, PostgreSQL), TOP (SQL Server), or FETCH FIRST (Oracle, standard SQL).

Practice Exercises

• Using the products table (product_id, product_name, price, category), write a query that returns the 2 most expensive products, showing product_name and price.
• Using the orders table (order_id, customer_id, order_date, amount), write a query that returns only the single most recent order. Hint: sort by order_date descending before limiting.
• Using the employees table, write a query that returns rows 2 and 3 (i.e. skip the highest earner, then show the next two) when sorted by salary descending. Hint: you’ll need both LIMIT and OFFSET.

Summary

• LIMIT caps the number of rows a query returns, and is applied after filtering, grouping, and sorting are all complete.
• Always combine LIMIT with ORDER BY — without it, which rows you get back is not guaranteed.
• OFFSET skips a number of rows before LIMIT starts counting, which is the basis of pagination.
• LIMIT must come after ORDER BY in the statement, matching SQL’s logical processing order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT).
• Add a unique tiebreaker column to your ORDER BY to make paginated or “top N” results reproducible.
• Other databases spell the same idea differently: TOP in SQL Server, FETCH FIRST in the SQL standard and Oracle.