Summary: In this chapter, we will learn how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.

Using MySQL LIMIT to Constrain The Number of Rows Returned By SELECT Statement

Introduction to MySQL LIMIT clause

If you need only a specified number of rows from a result set, use a LIMIT clause in the query. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.

The following illustrates the LIMIT clause syntax with two arguments:

SELECT
  column1,column2,...
FROM
  table
LIMIT OFFSET , COUNT;

Let’s examine the LIMIT clause parameters:

• The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
• The count specifies the maximum number of rows to return.

When we use LIMIT with one argument, this argument will be used to specifies the maximum number of rows to return from the beginning of the result set.

SELECT
 column1,column2,...
FROM
 table
LIMIT COUNT;

The above query is equivalent to the following query with the LIMIT clause that accepts two arguments:

SELECT
 column1,column2,...
FROM
 table
LIMIT 0 , COUNT;

MySQL LIMIT example

we can use the LIMIT clause to select the first 5 rows in a table as follows:

SELECT 
 firstname,
 lastname, 
 jobtitle
FROM 
 employees
LIMIT 5;

If we want to select 5 rows data after first two rows then we can use below query using LIMIT OFFSET , COUNT

SELECT 
 firstname,
 lastname, 
 jobtitle
FROM 
 employees
LIMIT 2,5;

In this chapter, we have shown you how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.