SQL LIMIT

What is SQL LIMIT?

LIMIT restricts the number of rows returned by a SELECT statement.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LIMIT: Supported by PostgreSQL, MySQL, MariaDB, SQLite, Snowflake. SQL Server and Oracle use TOP or FETCH FIRST instead.

SQL LIMIT Full Explanation

LIMIT is a row-limiting clause that tells the database to return only a specified number of rows from the query result set. It is evaluated after WHERE, GROUP BY, HAVING, and ORDER BY. When combined with OFFSET, it enables efficient keyset or page-based pagination. LIMIT is not part of the ANSI SQL-92 standard but is implemented by many popular databases, most notably PostgreSQL and MySQL. Because the clause executes after ORDER BY, omitting an ORDER BY can lead to non-deterministic result orders. Negative or NULL values are treated as zero and return no rows. LIMIT does not shorten query execution time if the database must still scan the full dataset to produce the result set; proper indexes and predicates are required for performance.

SQL LIMIT Syntax

SELECT column_list
FROM table_name
[WHERE condition]
[ORDER BY column_list]
LIMIT <limit_count> [OFFSET <offset_count>];

SQL LIMIT Parameters

limit_count (integer) - number of rows to return.
offset_count (integer) - number of rows to skip before starting to return rows. Optional.

Example Queries Using SQL LIMIT

-- Get the first 10 users
SELECT *
FROM users
ORDER BY id
LIMIT 10;

-- Skip the first 20 orders, then take 10
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

Expected Output Using SQL LIMIT

The first query returns only 10 rows from users ordered by id ascending
The second query returns rows 21-30 of the orders table when sorted by most-recent first

Use Cases with SQL LIMIT

Paginating large result sets in web or API responses
Sampling a small subset for testing
Protecting against runaway queries that would return millions of rows
Limiting dashboard widgets or reports to a top-N list

Common Mistakes with SQL LIMIT

Using LIMIT without ORDER BY and expecting consistent ordering
Forgetting OFFSET when implementing page numbers beyond the first page
Assuming LIMIT makes the query fast without proper indexes
Supplying LIMIT or OFFSET as negative values, which returns zero rows

First Introduced In

MySQL 3.23 (1998) and PostgreSQL 7.0 (2000)

Frequently Asked Questions

What does SQL LIMIT do?

LIMIT restricts how many rows the database sends back, returning at most the specified number.

Can I use LIMIT without OFFSET?

Yes. LIMIT alone returns the first n rows. Add OFFSET when you need to skip a certain number of rows.

Does LIMIT guarantee order?

Only if you include an ORDER BY clause. Without ORDER BY, the row order is undefined and may change between executions.

How do I calculate OFFSET for page-based pagination?

OFFSET equals (page_number - 1) multiplied by page_size. For example, page 3 with 20 rows per page uses OFFSET 40.