EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Limit

MySQL Limit

By Payal UdhaniPayal Udhani

MySQL Limit

Introduction to MySQL Limit

The limit clause in Mysql restricts the number of rows retrieved in the query resultset to a specific count. It also helps us to retrieve the particular number of records beginning from a specific offset from the MySQL table. Whenever the tables contain a huge amount of records, it becomes a heavy operation and even time-consuming to retrieve all these records and display them. This affects the performance of the application.

Hence, we can use the pagination technique to retrieve the result from the query in small chunks and display a certain number of records on each page. This can be done by using the limit clause. In this article, we will learn about the syntax of the limit clause and its usage, along with the examples in Mysql.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax of MySQL Limit

The limit clause accepts two arguments. One of them, count_of_rows is mandatory, while the other, the offset is optional. The syntax of the LIMIT clause and the place where it should be used are shown below:

SELECT
selected_columns_or_expressions
FROM
name_of_table
LIMIT [offset,] count_of_rows;

The count_of_rows is an integer value specifying the number of records retrieved from the name_of_table table. The offset is the optional integer value with the default value of 0. The offset can be used to specify the position of the record from which the result set should be fetched from the table named name_of_table. The following figure will help you to understand the offset and row_count concepts clearly –

LIMIT [offset,] count

Consider that the above diagram shows the table’s records and its numbers represent the row number of that records. If the offset is specified as 3, the row count that will begin will be from the fourth row as the offset begins from 0 by default. If we set the row_count to 4 and define the starting offset as the fourth row, the database will retrieve four records as specified by the row_count. If we had set the row_count to 5, the database would retrieve five rows starting from the fourth record, with row numbers 4, 5, 6, 7, and 8.

If we don’t specify an offset value, the database considers the default value of 0, and the row count starts from the first row, which has a number of 1. In that case, both of the following limit clauses work in the same way-

LIMIT 5;

and

LIMIT 0,5;

They will retrieve the first five records from the select query’s resultset.

Alternative Syntax:

The LIMIT clause has an additional optional syntax to maintain compatibility with the PostgreSQL syntax. This syntax is as follows:

LIMIT count_of_rows OFFSET offset;

Using LIMIT clause along with ORDER BY clause:

If a user retrieves records without using the ORDER BY clause, the resulting set will be returned in any order. Applying the LIMIT clause to such queries will result in random rows being returned, which is generally not desirable. The order in which the rows are retrieved is essential in specifying what records will be retrieved. Using the LIMIT clause and the ORDER BY clause is a good practice.

To retrieve records in a specific order and restrict the number of rows returned, you can use the ORDER BY clause with the field on which you want to order the data, followed by the LIMIT clause. The figure below illustrates the order in which the clauses in a SELECT statement are executed:

SELECT statement

We can observe that the database executes all the clauses before the LIMIT clause. When retrieving a result set, specify the row count and offset you want to retrieve.

Examples of MySQL Limit

The following examples illustrate the use of limits in MySQL:

Example #1

Let us create one table named educba_writers using the following query statement:

Code:

CREATE TABLE 'educba_writers' (
'id' int(11) NOT NULL,
'firstName' varchar(10) COLLATE latin1_danish_ci NOT NULL,
'rate' decimal(5,2) DEFAULT NULL,
'joining_date' date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

Output:

MySQL Limit-1.1

Example #2

Let us insert some more rows with non-null rate and joining date value –

Code:

INSERT INTO 'educba_writers' ('id', 'firstName', 'rate', 'joining_date') VALUES
(1, 'Payal', 750, "2020-05-01"),
(2, 'Vyankatesh', 700, "2020-01-01"),
(3, 'Omprakash', 600, "2020-02-01"),
(4, 'sakshi', 200, "2020-06-01"),
(5, 'prerna', 150, "2020-02-01"),
(6, 'preeti', 350, "2020-06-01"),
(7, 'sanjana', 400, "2020-02-01"),
(8, 'omkar', 450, "2020-06-01"),
(9, 'sohail', 650, "2020-02-01"),
(10, 'soniya', 850, "2020-06-01"),
(11, 'supriya', 700, "2020-02-01"),
(12, 'saniya', 750, "2020-06-01"),
(13, 'omkar', 410, "2020-02-01"),
(14, 'akshay', 910, "2020-06-01"),
(15, 'akash', 730, "2020-02-01"),
(16, 'siddharth', 980, "2020-06-01");

Output:

MySQL Limit-1.2

Example #3

Suppose we want to retrieve the list of the five highest-rated writers from the educba_writers table. For this, it is necessary to order the resultset based on the rate column in descending order so that the highest paid of them will be the first one to be retrieved. Further, as we have to retrieve only the top 5 writers, we will use the limit clause for specifying the same. Our query will be as follows –

Code:

select firstName, rate
FROM
educba_writers
ORDER BY rate DESC
LIMIT 5;

Output:

Example-1.3

Example #4

We can even implement the pagination using the limit clause. For this, we must first determine the number of records in the table. Users can determine the number of rows in a table that satisfies a specific condition by using the COUNT() function in the following way:-

Code:

SELECT COUNT(*) from educba_writers;

Output:

MySQL Limit-1.4

Further, we need to consider the size of the page, that is, the number of records that will be displayed on a single page. Suppose, in my case page size is 5. The total number of pages will be the total records or page size. In this case, we can determine that we will need four pages because 16/5 equals 3.2, which means the fourth page will only have one record. After that, we can specify the limit clause for the first five as LIMIT 5; for the next five as LIMIT 5,5; for the next as LIMIT 10,5.

Conclusion

In MySQL, you can use the LIMIT clause to restrict the number of records retrieved from the result set. This clause also helps us to specify the offset from where the row counting should start while retrieval and select the row count, which tells how many rows are to be retrieved.

Recommended Articles

We hope that this EDUCBA information on “MySQL Limit” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. IF Statement in MySQL
  2. MySQL Timestamp
  3. MySQL IN Operator
  4. ROLLUP in MySQL
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more