Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Limit Clause

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Limit
MySQL Limit Clause
The LIMIT clause in MySQL can be used to specify the number of records to return. This clause is mostly used when dealing with tables that have thousands of records. It accepts one or two arguments (offset or count). The values of both arguments should be either be positive integers or zero.
The offset of the first row starts from 0, not from 1 and the count of the first row starts from 1. Let us understand it better using the following picture:
﻿
Assume the name of the above table is students. If we execute the above-mentioned query, we will get the output as Mahika, Aarohi, and Nikhil.
Syntax
Following is the generic syntax of MySQL Limit clause 
SELECT column1, column2, ... FROM table_name
LIMIT number;
Where, the LIMIT clause specifies the maximum number of rows from the table to return.
Example
The following example demonstrates the usage of the MySQL Limit query.
First of all, let us create a table named CUSTOMERS using the following query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);
Here, we are inserting 7 records into the above-created table using the following INSERT INTO statement 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
Execute the following query to fetch all the records present in the CUSTOMERS table 
SELECT * FROM CUSTOMERS;
Following is the CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Now, we are selecting the first four records from the CUSTOMERS table using the MySQL LIMIT clause in conjunct with SELECT statement 
SELECT * FROM CUSTOMERS LIMIT 4;
Output
As we can see the output below, it returned the first four rows from the CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
Example
In the following query, we are selecting rows from the CUSTOMERS table starting from the third row (offset 2) from then four rows 
SELECT * FROM CUSTOMERS LIMIT 2,4;
Output
When we execute the above query, the output is obtained as follows 
ID
NAME
AGE
ADDRESS
SALARY
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
LIMIT with WHERE Clause
In MySQL, we can use the LIMIT clause along with the WHERE clause in a SELECT statement to specify the number of rows returned from the query based on the conditions.
Syntax
Following is the generic syntax 
SELECT column1, column2, ... FROM table_name
WHERE condition
LIMIT number;
Example
In the query below, we are selecting the first two rows from the CUSTOMERS table where the AGE is greater than 21 
SELECT * FROM CUSTOMERS WHERE AGE > 21 LIMIT 2;
Output
On executing the above query, the output is displayed as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
Example
In the following query, we are selecting the next 3 records from the CUSTOMERS table starting from the 2nd record (off set) where the value of the AGE column is greater than 21:
SELECT * FROM CUSTOMERS WHERE AGE > 21 LIMIT 1,3;
Output
The output for the above query is produced as given below 
ID
NAME
AGE
ADDRESS
SALARY
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
LIMIT with ORDER BY clause
The ORDER BY clause will sort the rows of a column in the specified order (ASC or DESC). In MySQL, we can use the LIMIT clause along with the ORDER BY clause to limit the number of rows returned in the sorted result set.
Syntax
Following is the syntax of LIMIT clause with WHERE clause in MySQL 
SELECT column1, column2, ... FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT number;
Example
In the below query, we are fetching all the records from the CUSTOMERS table and sorting the SALARY column in descending order. Then we are fetching 5 rows from the sorted result set.
SELECT * FROM CUSTOMERS
ORDER BY SALARY DESC
LIMIT 5;
Output
If we compile and run the above query, the result is produced as follows 
ID
NAME
AGE
ADDRESS
SALARY
7
Muffy
24
Indore
10000.00
5
Hardik
27
Bhopal
8500.00
4
Chaitali
25
Mumbai
6500.00
6
Komal
22
Hyderabad
4500.00
1
Ramesh
32
Ahmedabad
2000.00
Example
Here, we are selecting all the records from the table and sorting the SALARY column in ascending order. Then we are fetching rows from the sorted result set starting from the second row (offset 1) from then three rows 
SELECT * FROM CUSTOMERS
ORDER BY SALARY ASC
LIMIT 1,3;
Output
On executing the given query, the output is displayed as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
3
Kaushik
23
Kota
2000.00
6
Komal
22
Hyderabad
4500.00
Limit Clause Using a Client Program
In addition to limiting the number of records returned from a table with a MySQL query, we can also use a client program to perform the LIMIT operation.
Syntax
Following are the syntaxes of this operation in various programming languages 
PHPNodeJSJavaPython
To limit the number of records returned from a table through a PHP program, we need to execute the 'SELECT' statement with 'LIMIT' clause using the mysqli function query() as follows 
$sql = "SELECT * FROM tutorials_tbl WHERE tutorial_title = 'Java Tutorial' LIMIT 3";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = "SELECT * FROM tutorials_tbl WHERE tutorial_title = 'Java Tutorial' LIMIT 3";
if($result = $mysqli->query($sql)){
   printf("SELECT LIMIT statement executed successfully..!  ");
   printf("Records are(limit 3): ");
   while($row = mysqli_fetch_row($result)){
      print_r ($row);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows 
SELECT LIMIT statement executed successfully..!  Records are(limit 3): Array
(
    [0] => 1
    [1] => Java Tutorial
    [2] => new_author
    [3] =>
)
Array
(
    [0] => 3
    [1] => Java Tutorial
    [2] => newauther1
    [3] => 2023-12-20
)
Array
(
    [0] => 4
    [1] => Java Tutorial
    [2] => newauther2
    [3] => 2022-06-10
)  
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Limit

MySQL Limit Clause

Syntax

Example

Output

Example

Output

LIMIT with WHERE Clause

Syntax

Example

Output

Example

Output

LIMIT with ORDER BY clause

Syntax

Example

Output

Example

Output

Limit Clause Using a Client Program

Syntax

Example

Output