Skip to main content
HomeTutorialsSQL

How to Best Use the SQL LIMIT Clause

Learn the application of the SQL LIMIT clause to filter data. Master the use of the LIMIT clause in PostgreSQL and MySQL databases.
Jul 2024  · 8 min read

The SQL LIMIT clause controls the number of records returned in a query. As the name suggests, the LIMIT clause restricts the rows shown after filtering data. This clause is important when handling large datasets, but you only want to retrieve specific data portions.

If you are an aspiring data practitioner, I highly recommend taking DataCamp’s Introduction to SQL and SQL Fundamentals courses to gain relevant skills in SQL for data analysis.

The Short Answer: What is the SQL LIMIT Clause?

The SQL LIMIT clause is a useful clause that specifies the number of records a query should return after filtering data. This technique allows one to return only a subset of data for display or analysis. Therefore, the LIMIT clause is important for breaking down large data into smaller chunks in data pagination. It is also used for performance optimization to avoid large queries that could slow down database performance.

The example below shows the basic use of the LIMIT clause to show only the top 10 records from the employees table.

-- Select all columns from the employees table
-- and limit the number of returned rows to 10.
SELECT * 
FROM employees
LIMIT 10;

Syntax of the SQL LIMIT Clause

The SQL LIMIT clause is included in the SELECT statement to control the number of records returned from a query. The LIMIT clause is added at the end of the statement to filter the records as specified. You can also check DataCamp’s SQL Basics Cheat Sheet to learn more about the LIMIT and other common SQL clauses.

-- Select columns column1, column2, ... from the table table_name
SELECT column1, column2, ...
FROM table_name
-- Limit the number of rows returned to number_of_rows
LIMIT number_of_rows;

Here is the LIMIT clause in a practical example. The query will show only 10 records of the employees table from the first_name, last_name, and department columns.

-- Select first_name, last_name, and department from the employees table
SELECT first_name, last_name, department
FROM employees
-- Limit the number of rows returned to 10
LIMIT 10;

Sometimes, you can use the optional OFFSET clause to skip a specific number of rows before displaying the intended rows. The OFFSET syntax is shown below.

-- Select column_names from the table table_name
SELECT column_names
FROM table_name
-- Arrange the results in ascending order based on column_name
ORDER BY column_name
-- Limit the number of rows returned to number_of_rows, starting from offset_value
LIMIT number_of_rows OFFSET offset_value;

In the above SQL query, the number_of_rows indicates the number of rows to return, while the offset_value shows the number of rows the query should skip before returning them. 

The example below shows how to use SQL OFFSET and LIMIT clauses to control the number of rows to return. While the LIMIT clause specifies returning 10 records, the OFFSET clause in the query skips the first 5 records. Therefore, the results will display records from the 6th row of the data to the 15th row (10 rows).

-- Select first_name, last_name, and department from the employees table
SELECT first_name, last_name, department
FROM employees
-- Limit the number of rows returned to 10, starting from the 6th row (offset of 5)
LIMIT 10 OFFSET 5;

Important Points to Remember About the SQL LIMIT Clause

Understanding how to properly implement the LIMIT clause and its variations across different SQL databases is crucial for optimizing query performance and ensuring accurate data retrieval. Let's take a look at some important points. 

The LIMIT clause placement in the SELECT statement

The LIMIT clause is usually placed at the end of the SELECT statement after the FROM clause. If you use other optional aggregation clauses such as WHERE, GROUP BY, and ORDER BY clauses, ensure the LIMIT clause is at the end of the query.

-- Select all columns from the employees table
SELECT *
FROM employees
-- Limit the number of rows returned to 5, starting from the 21st row (offset of 20)
LIMIT 10 OFFSET 20;

Combining LIMIT with ORDER BY clause

The LIMIT clause can be combined with the ORDER BY aggregate for consistent and predictable results. This technique ensures the data are stored in a particular order before retrieving them. In the example below, we order the data by the hire_date in descending order before showing the filtered rows.

-- Select all columns from the employees table
SELECT *
FROM employees
-- Arrange the results in descending order based on hire_date
ORDER BY hire_date DESC
-- Limit the number of rows returned to 10
LIMIT 10;

I would recommend taking DataCamp’s Associate Data Analyst in SQL career track and Reporting in SQL course to learn more about aggregate functions and their applications in data analysis.

SQL LIMIT variations and syntax across databases

The above examples show the use of SQL LIMIT clauses in supported PostgreSQL, SQLite, and MySQL databases.

To achieve similar results in SQL Server, consider using the TOP function to control the number of records to return from given data.

-- Select the top 10 records of first_name, last_name from the employees table
SELECT TOP 10 first_name, last_name
FROM employees
-- Arrange the results in descending order based on hire_date
ORDER BY hire_date DESC;

Similarly, the Oracle database supports using FETCH FIRST n ROWS ONLY to control the number of records to return in a query.

-- Select the first_name and last_name columns from the employees table
SELECT first_name, last_name
FROM employees
-- Order the results in descending order based on hire_date
ORDER BY hire_date DESC
-- Limit the number of rows returned to the first 10 rows
FETCH FIRST 10 ROWS ONLY;

LIMIT and OFFSET in pagination

SQL LIMIT and OFFSET are usually combined to achieve efficient data pagination. This technique ensures large datasets are broken down into smaller, manageable chunks for appropriate display.

In the example below, the LIMIT and OFFSET clause limits the displayed rows to 10 but starts from the 21st row.

-- Select all columns from the employees table
SELECT *
-- Arrange the results in descending order based on hire_date
FROM employees
ORDER BY hire_date DESC
-- Limit the number of rows returned to 10, starting from the 21st row (offset of 20)
LIMIT 10 OFFSET 20;

Common Use Cases of the SQL LIMIT Clause

  • Pagination: When combined with the OFFSET clause, the LIMIT clause is used in web applications for data pagination. This technique displays data in smaller chunks/subsets, such as on different web pages.
  • Optimizing Performance: The LIMIT clause can improve query performance by reducing the load on the database servers. It achieves this technique by fetching and returning smaller data subsets simultaneously.
  • Data Sampling: The LIMIT clause is useful in data analysis by fetching smaller data subsets. This method helps data analysts to improve performance during analysis.

Limitations of the SQL Limit Command

The limitations of the SQL LIMIT clause include the following:

  • Lack of Support by All Databases: The LIMIT clause is not supported in all databases. For instance, the LIMIT clause is unavailable in SQL Server and Oracle databases. 
  • Performance Issues with Large OFFSET values: Using large OFFSET values can slow down database servers, resulting in slower query execution.

To avoid the limitations of different databases, consider using the following alternatives.x

The TOP clause in SQL Server

The TOP clause gives the same results as the LIMIT clause for the SQL Server database.

-- Select the top 10 rows from the employees table ordered by hire_date
SELECT TOP 10 first_name, last_name
FROM employees
ORDER BY hire_date DESC;

FETCH FIRST n ROWS ONLY in SQL Server and DB2

The FETCH FIRST n ROWS ONLY also returns results similar to those of the LIMIT clause for the SQL Server and DB2 databases.

-- Select the first 10 rows from the employees table ordered by hire_date
SELECT first_name, last_name
FROM employees
ORDER BY hire_date DESC
FETCH FIRST 10 ROWS ONLY;

ROWNUM in Oracle

In the Oracle database, the ROWNUM function controls the number of records returned from data. The function is combined in a subquery to filter the data as required.

-- Select the top 10 rows from the employees table ordered by hire_date
SELECT * FROM (
    SELECT first_name, last_name
    FROM employees
    ORDER BY hire_date DESC
) WHERE ROWNUM <= 10;

Conclusion

The SQL LIMIT clause is important for restricting the number of rows displayed after retrieving data from a database. As a data practitioner, it is important to understand the different use cases of the LIMIT clause for data analysis and manipulation. Similarly, you should consider the database support for the LIMIT clause and use other alternative functions when using incompatible databases like SQL Server and Oracle.

To understand the different applications of the SQL LIMIT clause, you should continue practicing with different datasets. DataCamp’s Learn SQL and Intermediate SQL courses are appropriate for gaining advanced skills in SQL. I also recommend taking DataCamp’s SQL Associate Certification to demonstrate your knowledge of using SQL and stand among other data practitioners.


Photo of Allan Ouko
Author
Allan Ouko
I create articles that simplify data science and analytics, making them easy to understand and accessible.

Frequently Asked Questions

What is the use of the SQL LIMIT function?

The SQL LIMIT function controls the number of rows returned from filtered data.

What is the main use of the SQL LIMIT?

SQL LIMIT is mainly used for data pagination on web pages to restrict the number of rows on display.

Is the SQL LIMIT clause supported in all databases?

The SQL LIMIT function is supported in only PostgreSQL, MySQL, and SQLite databases. The clause is not supported in the SQL Server and Oracle databases.

What is the alternative to the LIMIT clause in the unsupported databases?

The alternative to SQL LIMIT function in SQL Server is TOP and FETCH FIRST n ROWS ONLY clauses. The Oracle database uses the ROWNUM function as the alternative to the LIMIT.

What is the use of OFFSET in the SQL LIMIT clause?

We can use OFFSET to specify the number of rows to skip before returning the particular number of rows from the LIMIT clause.

Topics

Learn SQL with DataCamp

Course

Introduction to SQL

2 hr
725.9K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

tutorial

How to Use SQL OFFSET

Master the SQL OFFSET clause. Discover how to use ORDER BY and LIMIT to return values based on position, with practical examples, including data pagination for web applications.
Allan Ouko's photo

Allan Ouko

10 min

tutorial

SQL: Reporting and Analysis

Master SQL for Data Reporting & daily data analysis by learning how to select, filter & sort data, customize output, & how you can report aggregated data from a database!
Hafsa Jabeen's photo

Hafsa Jabeen

37 min

tutorial

How to Use GROUP BY and HAVING in SQL

An intuitive guide for discovering the two most popular SQL commands to aggregate rows of your dataset
Eugenia Anello's photo

Eugenia Anello

6 min

tutorial

SELECTing Multiple Columns in SQL

Learn how to easily select multiple columns from a database table in SQL, or select all columns from a table in one simple query.
DataCamp Team's photo

DataCamp Team

3 min

tutorial

How to Use the SQL BETWEEN Operator

The SQL BETWEEN operator helps filter values within specific ranges, enabling efficient data analysis. Discover its various applications and full potential.
Allan Ouko's photo

Allan Ouko

10 min

code-along

Getting Started in SQL

Learn how to write basic queries in SQL and find answers to business questions.
Kelsey McNeillie's photo

Kelsey McNeillie

See MoreSee More