VMware ESXi and vSphere Cluster Management

SQL SELECT LIMIT: Return a Limited Number of Rows

Learn how to use SQL LIMIT with SELECT queries, ORDER BY, WHERE, OFFSET, pagination, and database-specific row-limiting syntax.

The SQL LIMIT clause restricts the maximum number of rows returned by a SELECT query. It is useful when you need a preview, a small sample, a test result, or one page of a larger result set.

A result set is the group of rows returned by a query. LIMIT changes the result set produced by the query; it does not delete rows or change the data stored in the table.

What LIMIT Does

LIMIT sets an upper boundary on the number of rows in the result. For example, LIMIT 2 requests no more than two rows.

LIMIT is different from WHERE:

• WHERE determines which rows are eligible by applying a condition.
• LIMIT caps how many eligible rows are returned.

Common uses include:

• Previewing a table before writing a larger query.
• Testing a query without displaying a large result.
• Showing a small sample in an application or report.
• Returning page-sized groups of rows for pagination.

Basic LIMIT Syntax

The basic pattern is:

SELECT column_list
FROM table_name
LIMIT row_count;

column_list contains the columns to retrieve. Use SELECT * when all columns are needed:

SELECT *
FROM table_name
LIMIT row_count;

row_count is a non-negative integer representing the maximum number of rows requested. LIMIT does not promise that many rows; if fewer rows are available, the query returns only the available rows.

Sample Employees Table

The examples use an employees table with employee numbers and names:

Return Two Employees

To return no more than two rows, use LIMIT 2:

SELECT *
FROM employees
LIMIT 2;

A possible result is:

This query returns a maximum of two employee records. However, LIMIT alone does not define which rows are considered first. The database may return a different pair if the query is run again or the execution plan changes.

Use ORDER BY for Predictable Results

ORDER BY sorts a result set. Add it whenever the limited subset must have a specific and repeatable meaning.

Lowest employee numbers

SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY employeeNumber ASC
LIMIT 2;

ASC sorts from lowest to highest. This query returns the two employees with the lowest employee numbers:

Highest employee numbers

SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY employeeNumber DESC
LIMIT 2;

DESC sorts from highest to lowest, so this query returns the two highest employee numbers:

Use LIMIT with Selected Columns

You do not need to select every column. Select only the employee identity columns needed by the application:

SELECT employeeNumber, lastName, firstName
FROM employees
LIMIT 2;

This limits rows just as SELECT * does, while returning a narrower set of columns. Choosing specific columns can make results easier to read and avoids transferring unnecessary data.

Use LIMIT with WHERE

You can combine a filter with a row limit. The WHERE clause identifies matching rows, and LIMIT then caps the number returned.

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastName = 'Patterson'
ORDER BY employeeNumber ASC
LIMIT 2;

In this example, only employees whose last name is Patterson are eligible. The ordered eligible rows are then limited to two. If only one matching employee exists, the query returns one row even though LIMIT is 2.

The conceptual processing is:

1. Read rows from employees.
2. Keep rows satisfying the WHERE condition.
3. Sort the matching rows with ORDER BY.
4. Return no more than the LIMIT count.

LIMIT is therefore not a replacement for WHERE. A query such as LIMIT 2 does not mean “find two rows matching a condition”; it only restricts the final output.

OFFSET and Pagination

OFFSET specifies how many qualifying rows to skip before returning rows. Pagination retrieves a large ordered result in smaller page-sized groups.

A common form, supported by several SQL products, is:

SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY employeeNumber ASC
LIMIT 2 OFFSET 2;

This skips the first two ordered employees and returns up to the next two. With the sample data, the second page contains employee numbers 1076 and 1088.

MySQL-compatible syntax can place the offset first inside LIMIT:

SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY employeeNumber ASC
LIMIT 2, 2;

In this form, the first value is the offset and the second value is the row count: skip 2 rows and return up to 2 rows.

If the sort column can contain ties, add a deterministic tie-breaker. For example:

SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY lastName ASC, employeeNumber ASC
LIMIT 2 OFFSET 2;

LIMIT and SQL Dialects

A SQL dialect is a database product’s variation of SQL syntax and features. LIMIT is common, but it is not universal SQL syntax.

For example, a SQL Server query may use:

SELECT TOP (2) employeeNumber, lastName, firstName
FROM employees
ORDER BY employeeNumber ASC;

A standard-oriented row-limiting form may look like:

SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY employeeNumber ASC
FETCH FIRST 2 ROWS ONLY;

Check the documentation for the database product and version you are connected to before using LIMIT syntax.

Common Mistakes and Limitations

Expecting LIMIT to choose guaranteed rows

Problem: The query uses LIMIT without ORDER BY and returns different rows on different executions.

Fix: Add an ORDER BY clause based on the intended sequence, such as ORDER BY employeeNumber ASC.

Using LIMIT instead of WHERE

Problem: LIMIT is expected to find rows matching a condition.

Fix: Use WHERE to identify eligible rows, then use LIMIT to cap the output.

Using an invalid row count

The row count should be a supported non-negative integer. Negative values, non-integer values, or unsupported parameters can cause an error or database-specific behavior.

Assuming every database supports LIMIT

If the database reports a syntax error near LIMIT, use its equivalent, such as TOP, FETCH FIRST, or OFFSET ... FETCH.

Confusing LIMIT with a data-changing operation

LIMIT affects the rows returned by a query. It does not remove rows, update values, or change the table. Operations such as DELETE and UPDATE have separate syntax and effects.

Receiving fewer rows than requested

This is expected when the table or filtered result contains fewer rows than the LIMIT value, or when OFFSET skips most of the available rows. LIMIT is a maximum, not a guarantee.

Unstable pagination

Duplicate or missing records between pages usually indicate missing ordering or ties in the sort columns. Use ORDER BY with a unique or otherwise deterministic tie-breaker, such as ORDER BY lastName, employeeNumber.

Quick Reference

For a related overview, see SQL SELECT LIMIT Statement.