MySQL - Limit

In this tutorial, you will learn how to use MySQL LIMIT clause to select records from the beginning, middle and end of a result set.
 
Syntax
 
The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integer constants.
The following illustrates the LIMIT clause syntax with 2 arguments:
 
SELECT * FROM tbl
LIMIT offset, count
 
Let’s see what the offset and count mean in the LIMIT clause:
The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
The count specifies maximum number of rows to return.
 
When you 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 * FROM tbl
LIMIT count
 
The query above is equivalent to the following query with the LIMIT clause that accepts two arguments:
 
SELECT * FROM tbl
LIMIT 0, count
 
Using MySQL LIMIT to get the first N rows
 
You can use the LIMIT clause to select the first N rows in a table as follows:
 
SELECT * FROM tbl
LIMIT N
 
For example, to select the first 10 customers, you use the following query:
 
SELECT customernumber,
       customername,
       creditlimit
FROM customers
LIMIT 10;
 
Using MySQL LIMIT to get the highest and lowest values
 
The LIMIT clause often used with ORDER BY clause. First, you use the ORDER BY clause to sort the result set based on a certain criteria, and then you use LIMIT clause to find lowest or highest values.
 
For example, to select 5 customers who have the highest credit limit, you use the following query:
 
SELECT customernumber,
       customername,
       creditlimit
FROM customers
ORDER BY creditlimit DESC
LIMIT 5;
 
And the following query returns 5 customers who have the lowest credit limit:
 
SELECT customernumber,
       customername,
       creditlimit
FROM customers
ORDER BY creditlimit ASC
LIMIT 5;
 
Using MySQL LIMIT to get the N highest values
 
One of the toughest questions in MySQL is how to select the N highest values in a result set e.g., select the second most expensive product, which you cannot use MAX or MIN functions to answer. However, you can use MySQL LIMIT to answer those kinds of questions.
 
Let’s take a look at the products result set of the following query:
 
SELECT productName,
       buyprice
FROM products
ORDER BY buyprice DESC;
 
Our task is to get the highlight product, which is the second most expensive product in the products result set. In order to do so, you use LIMIT clause to select 1 row from the second row as the following query: (notice that the offset starts from zero)
 
SELECT productName,
       buyprice
FROM products
ORDER BY buyprice DESC
LIMIT 1, 1
 
In this tutorial, you have learned how to use MySQL LIMIT clause to select records from the beginning, the middle and the end of a result set.