The MySQL LIMIT is used to restrict the number of rows that are returned by the query. For example, If you are Sales Manager of a statements to identify the top performing products then you can use MySQL Limit.
To return few records or to restrict the rows returned by the Select statement, we have to use this LIMIT at the end of the query. The basic Syntax of the MySQL LIMIT clause is as shown below
SELECT Column_Names]
FROM [Table_Name]
LIMIT Value
MySQL Limit * Example
For this Limit Demonstration, We are going to use the below-shown data.
Here, Select * statement selects all the records from customers. However, LIMIT 1 will limit or restrict the records to 1 row.
SELECT *
FROM `MySQL Tutorial`.customer
LIMIT 1;
OUTPUT
MySQL Limit 10 Example
This query limits the selected records to 10. Use this query to select top 10 records from a customer table.
SELECT *
FROM `MySQL Tutorial`.customer
LIMIT 10;
OUTPUT
MySQL Limit 3 Example
As we said earlier, it is not a good practice to use Select *. This example selects the required columns from customers. Limit will select first three records.
SELECT EmpID,
`First Name`,
`Last Name`,
Qualification,
Occupation,
Income,
Sales,
HireDate
FROM `MySQL Tutorial`.customer
LIMIT 3;
OUTPUT
MySQL Limit ORDER BY Statement
By default, table data will be sorted by the index values. To retrieve three customers having the highest income or sales, you have to sort the data using ORDER BY. Next, use this Limit to get the top three customers
SELECT EmpID,
`First Name`,
`Last Name`,
Qualification,
Occupation,
Income,
Sales,
HireDate
FROM `MySQL Tutorial`.customer
ORDER BY Income
LIMIT 3;
OUTPUT
This time we are using the DESC keyword. It means, data will be sorted by Income in descending order. Next, Limit will select the top 7 records.
SELECT EmpID,
`First Name`,
`Last Name`,
Qualification,
Occupation,
Income,
Sales,
HireDate
FROM `MySQL Tutorial`.customer
ORDER BY Income DESC
LIMIT 7;OUTPUT
MySQL Limit Where Example
You can also use Where Clause along with Limit. This will first check the condition against the table, and then select the required rows.
The following query selects the First 5 Customers whose Sales is Greater than 2000. Here, we used the Order By clause to sort the Customers in Descending order using income.
SELECT EmpID,
`First Name`,
`Last Name`,
Qualification,
Occupation,
Income,
Sales,
HireDate
FROM `MySQL Tutorial`.customer
WHERE Sales > 2000
ORDER BY Income DESC
LIMIT 5;
OUTPUT
MySQL Limit Offset Example
You can use the MySQL Row Offset along with this. Here, Limit 3, 1 means Starts at 4th and select 1 record.
SELECT EmpID,
`First Name`,
`Last Name`,
Qualification,
Occupation,
Income,
Sales,
HireDate
FROM `MySQL Tutorial`.customer
LIMIT 3, 1;
OUTPUT
The following query starts at 6th records, and selects consecutive 7 records.
SELECT EmpID,
`First Name`,
`Last Name`,
Qualification,
Occupation,
Income,
Sales,
HireDate
FROM `MySQL Tutorial`.customer
LIMIT 5, 7;
OUTPUT
The following MySQL limit query sort the customers in Descending ordering income. Next, it select the 3rd record. It means, third highest salaried customer.
SELECT EmpID,
`First Name`,
`Last Name`,
Qualification,
Occupation,
Income,
Sales,
HireDate
FROM `MySQL Tutorial`.customer
ORDER BY Sales DESC
LIMIT 2, 1;
OUTPUT
Limit Multiple Examples
In this example, we are using the Command Prompt. First, Limit 0 means it returns zero records. The second statement select first 2 records. The last statement select the first two customers order by sales in descending order. It means, two highest sales customers.
SELECT * FROM customer LIMIT 0;
SELECT * FROM customer LIMIT 2;
SELECT * FROM customer ORDER BY Sales DESC LIMIT 2;
OUTPUT
