MySQL LIMIT Example 11

MySQL LIMIT

The MySQL LIMIT is useful to restrict the number of rows that are returned by the query. For example, If you are the Sales Manager and your job is to identify the top-performing products, then you can use MySQL Limit.

If you want the Select statement to return a few records or to restrict the rows, we have to use this 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 demonstration, We are going to use the below shown data.

Source Table 1

Here, Select * statement selects all the records from customers. However, 1 restrict the records to 1 row.

SELECT *
FROM customer
LIMIT 1;
Select Top 1 Record Example 2

Top 10 Example

This MySQL LIMIT query restrict the selected records to 10. Use this query to select the top 10 records from a customer table.

SELECT *
FROM customer
LIMIT 10;
MySQL LIMIT 10 Example 3

Top 3 Records Example

As we said earlier, it is not a good practice to use Select *. This MySQL LIMIT 3 example selects the required columns from customers. This statement selects the first three records.

SELECT EmpID, 
       `First Name`,
        `Last Name`,
        Qualification,
        Occupation,
        Income,
        Sales,
        HireDate
FROM customer
LIMIT 3;
Select Top 3 Records Example 4

MySQL Limit ORDER BY Statement

By default, MySQL table data will sort 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 to get the top three customers

SELECT EmpID, 
       `First Name`,
        `Last Name`,
        Qualification,
        Occupation,
        Income,
        Sales,
        HireDate
 FROM customer
 ORDER BY Income
 LIMIT 3;
MySQL LIMIT Order By Example 5

This time we are using the DESC keyword. It means data sorted by Income in descending order. Next, it select the top 7 records.

SELECT EmpID, 
       `First Name`,
        `Last Name`,
        Qualification,
        Occupation,
        Income,
        Sales,
        HireDate
 FROM customer
 ORDER BY Income DESC
 LIMIT 7;
Select Bottom or Last Seven Records in a Table Example 6

MySQL Limit Where Example

You can also use Where Clause along with this. First, it checks the condition against the table and then selects 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 customer
 WHERE Sales > 2000
 ORDER BY Income DESC
 LIMIT 5;
MySQL LIMIT Example 7

Offset Example

You can use the MySQL Row Offset along with the LIMIT. Here, 3, 1 means Starts at 4th and select 1 row.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 LIMIT 3, 1;
MySQL LIMIT Order by 8

The following query starts at 6th records, and print consecutive 7 records.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 LIMIT 5, 7;
MySQL LIMIT Example 9

The following MySQL limit query sort the customers in Descending ordering income. Next, it prints the 3rd row. It means the third highest salaried customer.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 ORDER BY Sales DESC
 LIMIT 2, 1;
MySQL LIMIT Example 10

Multiple Examples

In this example, we are using the Command Prompt. First, 0 means it returns zero records. The second statement returns the first 2 rows. The last statement returns the first two customers order by sales in descending order. It means the two highest sales customers.

MySQL LIMIT Example 11