SQL LIMIT

Summary: in this tutorial, you’ll learn to use the SQL LIMIT clause to limit the number of rows returned from a query.

Introduction to SQL LIMIT clause

To limit the number of rows returned by a select statement, you use the LIMIT and OFFSET clauses.

The following shows the syntax of LIMIT & OFFSET clauses:

SELECT 
    column_list
FROM
    table1
ORDER BY column_list
LIMIT row_count OFFSET offset;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The LIMIT row_count determines the number of rows (row_count) returned by the query.
• The OFFSET offset clause skips the offset rows before beginning to return the rows.

The OFFSET clause is optional. If you omit it, the query will return the row_count rows from the first row returned by the SELECT clause.

When you use the LIMIT clause, it is important to use an ORDER BY clause to ensure the order of rows in the result set.

Not all database systems support the LIMIT clause. Therefore, the LIMIT clause is available only in some database systems only such as MySQL, PostgreSQL, SQLite, Sybase SQL Anywhere, and HSQLDB. If you use SQL Server, you can use the SELECT TOP instead.

SQL LIMIT clause examples

We’ll use the employees table in the sample database to demonstrate the LIMIT & OFFSET clauses.

The following statement returns all rows in the employees table sorted by the first_name column.