Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

MySQL LIMIT

by suresh

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 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.

MySQL LIMIT Example 1

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

SELECT *
FROM `MySQL Tutorial`.customer
LIMIT 1;

OUTPUT

MySQL LIMIT Example 2

MySQL Limit 10 Example

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

SELECT *
FROM `MySQL Tutorial`.customer
LIMIT 10;

OUTPUT

MySQL LIMIT Example 3

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. The limit statement selects the first three records.

SELECT EmpID, 
`First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM `MySQL Tutorial`.customer
LIMIT 3;

OUTPUT

MySQL LIMIT 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 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

MySQL LIMITExample 5

This time we are using the DESC keyword. It means data sorted by Income in descending order. Next, Limit 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 Example 6

MySQL Limit Where Example

You can also use Where Clause along with Limit. 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 `MySQL Tutorial`.customer
WHERE Sales > 2000
ORDER BY Income DESC
LIMIT 5;

OUTPUT

MySQL LIMIT Example 7

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

MySQL LIMIT Order by 8

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

MySQL LIMIT Example 9

The following MySQL limit query sort the customers in Descending ordering income. Next, it selects the 3rd record. It means the 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

MySQL LIMIT Example 10

Limit Multiple Examples

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

SELECT * FROM customer LIMIT 0;

SELECT * FROM customer LIMIT 2;

SELECT * FROM customer ORDER BY Sales DESC LIMIT 2;

OUTPUT

MySQL LIMIT Example 11

Placed Under: MySQL

  • How to Download MySQL
  • Install MySQL on Windows
  • MySQL Create Database
  • MySQL Delete Database
  • MySQL Create Table
  • MySQL Drop Table
  • MySQL SELECT Statement
  • MySQL ALIAS Column
  • MySQL Distinct
  • MySQL Insert Statement
  • MySQL Delete
  • MySQL Truncate Table
  • MySQL WHERE Clause
  • MySQL Order By
  • MySQL Group By
  • MySQL Having Clause
  • MySQL LIMIT
  • MySQL Arithmetic Operators
  • MySQL COALESCE Function
  • MySQL AND Operator
  • MySQL NOT Operator
  • MySQL OR Operator
  • MySQL XOR Operator
  • MySQL BETWEEN Operator
  • MySQL Not Between Operator
  • MySQL GREATEST Function
  • MYSQL LEAST Function
  • MySQL LIKE Operator
  • MySQL NOT LIKE Operator
  • MySQL IFNULL Operator
  • MySQL NULLIF Operator
  • MySQL INTERVAL Operator
  • MySQL IS Operator
  • MySQL IN Operator
  • MySQL NOT IN Operator
  • MySQL IS NOT NULL
  • MySQL IS NULL
  • MySQL Inner Join
  • MySQL Cross Join
  • MySQL Right Join
  • MySQL Left Join
  • MySQL Aggregate Functions
  • MySQL Date Functions
  • MySQL Date Function
  • MySQL String Functions
  • MySQL Numeric Functions
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy