SQL online course

SQL SELECT LIMIT Statement

Learn how to use the SQL LIMIT clause with SELECT to return a small, controlled number of rows, with examples using employee data and ORDER BY.

What Is the SQL LIMIT Clause?

SELECT is the SQL statement used to retrieve data from one or more tables. A table is a database structure containing records organized into rows and columns. A column is a named data field in a table.

The LIMIT clause caps the number of rows included in a query's result set. The result set is the collection of rows produced by a query. The number after LIMIT is the requested row count, or maximum number of rows to return.

LIMIT is useful when you need to:

• Preview a table without displaying every record
• Test a query against a small result
• Display a short list of records
• Retrieve a sample-sized subset while developing an application

Basic SELECT LIMIT Syntax

Place LIMIT after the table name and after other applicable filtering or sorting clauses:

SELECT column_name1, column_name2
FROM table_name
LIMIT row_count;

For example, this query returns no more than five rows and includes every column:

SELECT *
FROM employees
LIMIT 5;

The asterisk (*) means that the query selects all columns. You can also name only the columns that you need:

SELECT employeeNumber, lastName, firstName
FROM employees
LIMIT 5;

Selecting named columns can make the result easier to read and can avoid transferring unnecessary data.

Employee-Table Example

Suppose an employees table contains these columns and records:

This query requests two employee records:

SELECT *
FROM employees
LIMIT 2;

The result set contains at most two rows:

If the table contains fewer than two rows, the query returns the rows that exist. LIMIT does not create rows to reach the requested count.

Selecting Specific Employee Columns

To return only the employee number, last name, and first name, write the column list explicitly:

SELECT employeeNumber, lastName, firstName
FROM employees
LIMIT 2;

This still limits the result set to two rows, but the result includes only the three named columns.

Use ORDER BY Before LIMIT

LIMIT alone does not define which rows are “first.” Without an ORDER BY clause, the database may return rows in an implementation-dependent order. That order can change between executions, after data changes, or after a database chooses a different query plan.

Use ORDER BY to sort the rows before LIMIT chooses the maximum number of results. For example, this query consistently requests the two lowest employee numbers:

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

ORDER BY sorts the rows, and ASC means ascending order. The database first orders employees by employee number from lowest to highest, then returns two rows.

For the example data, the result is:

Combining LIMIT with WHERE

A WHERE clause filters rows before the final result is limited. The usual order is SELECT, FROM, WHERE, ORDER BY, and LIMIT:

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

This query considers only employees whose last name is Brown, sorts those matching rows, and returns no more than two of them. See the SQL WHERE clause lesson for more filtering examples.

Database Dialect Awareness

LIMIT is supported by database systems such as MySQL, PostgreSQL, and SQLite. SQL syntax can vary between database products, so LIMIT is not universal SQL syntax.

If your database reports a syntax error near LIMIT, check the product's documentation and use its supported alternative. The exact syntax can also depend on the product version and query context.

Troubleshooting LIMIT Queries

The returned rows change between executions

Cause: The query uses LIMIT without ORDER BY.

Solution: Add ORDER BY with the column or columns that define the intended sequence:

SELECT *
FROM employees
ORDER BY employeeNumber ASC
LIMIT 2;

The database reports a syntax error near LIMIT

Cause: The selected database product may not support LIMIT syntax.

Solution: Use the product's row-limiting syntax, such as TOP or FETCH FIRST.

The query returns fewer rows than the LIMIT value

Cause: The table, or the rows that match a WHERE condition, contains fewer records than requested.

Solution: This is normal behavior. Check the available rows and review any filtering conditions.

Key Points

• LIMIT sets the maximum number of rows in a SELECT result.
• The number after LIMIT is the requested row count.
• Use SELECT * to return all columns or list column names to return selected fields.
• Place LIMIT after FROM, WHERE, and ORDER BY when those clauses are present.
• Use ORDER BY before LIMIT when the selected rows must be predictable.
• LIMIT syntax is common in MySQL, PostgreSQL, and SQLite, but other products may use TOP or FETCH FIRST.

For related fundamentals, review the SQL SELECT statement, SQL ORDER BY clause, and SQL LIKE operator.