MySQL Tutorials for Business Analyst: How to use Limit keyword in MySQL

What is the LIMIT keyword?

The limit keyword is used to limit the number of rows returned in a  query result.

It can be used in conjunction with the SELECT, UPDATE OR DELETE commands LIMIT keyword syntax

The syntax for the LIMIT keyword is as follows

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT  N;

HERE

  • “SELECT {fieldname(s) | *} FROM tableName(s)” is the SELECT statement containing the fields that we would like to return in our query.
  • “[WHERE condition]” is optional but when supplied, can be used to specify a filter on the result set.
  • “LIMIT  N” is the keyword and N is any number starting from 0, putting 0 as the limit does not return any records in the query. Putting a number say 5 will return five records. If the records in the specified table are less than N, then all the records from the queried table are returned in the result set.

Let’s look at an example –

SELECT *  FROM members LIMIT 2;
membership_ number full_ names gender date_of _birth date_of _registration physical_ address postal_ address contact_ number email credit_ card_ number
1 Janet Jones Female 21-07-1980 NULL First Street Plot No 4 Private Bag 0759 253 542 janetjones@yagoo.cm NULL
2 Janet Smith Jones Female 23-06-1980 NULL Melrose 123 NULL NULL jj@fstreet.com NULL

 

As you can see from the above screenshot, only two members have been returned.

Getting a list of ten (10) members only from the database

Let’s suppose that we want to get a list of the first 10 registered members from the Myflix database. We would use the following script to achieve that.

SELECT *  FROM members LIMIT 10;

Executing the above script gives us the results shown below

 

membership_ number full_ names gender date_of _birth date_of _registration physical_ address postal_ address contact_ number email credit_ card_ number
1 Janet Jones Female 21-07-1980 NULL First Street Plot No 4 Private Bag 0759 253 542 janetjones@yagoo.cm NULL
2 Janet Smith Jones Female 23-06-1980 NULL Melrose 123 NULL NULL jj@fstreet.com NULL
3 Robert Phil Male 12-07-1989 NULL 3rd Street 34 NULL 12345 rm@tstreet.com NULL
4 Gloria Williams Female 14-02-1984 NULL 2nd Street 23 NULL NULL NULL NULL
5 Leonard Hofstadter Male NULL NULL Woodcrest NULL 845738767 NULL NULL
6 Sheldon Cooper Male NULL NULL Woodcrest NULL 976736763 NULL NULL
7 Rajesh Koothrappali Male NULL NULL Woodcrest NULL 938867763 NULL NULL
8 Leslie Winkle Male 14-02-1984 NULL Woodcrest NULL 987636553 NULL NULL
9 Howard Wolowitz Male 24-08-1981 NULL SouthPark P.O. Box 4563 987786553 lwolowitz[at]email.me NULL

Note only 9 members have been returned in our query since N in the LIMIT clause is greater than the number of total records in our table.

Re-writing the above script as follows

SELECT *  FROM members LIMIT 9;

Only returns 9 rows in our query result set.

Using the OFF SET in the LIMIT query

The OFF SET value is also most often used together with the LIMIT keyword. The OFF SET value allows us to specify which row to start from retrieving data

 

Let’s suppose that we want to get a limited number of members starting from the middle of the rows, we can use the LIMIT keyword together with the offset value to achieve that. The script shown below gets data starting the second row and limits the results to 2.

SELECT * FROM `members` LIMIT 1, 2;

Executing the above script in MySQL workbench against the myflixdb gives the following results.

 

membership_ number full_ names gender date_of _birth date_of _registration physical_ address postal_ address contact_ number email credit_ card_ number
2 Janet Smith Jones Female 23-06-1980 NULL Melrose 123 NULL NULL jj@fstreet.com NULL
3 Robert Phil Male 12-07-1989 NULL 3rd Street 34 NULL 12345 rm@tstreet.com NULL
	
Note that here OFFSET = 1 Hence row#2 is returned & Limit = 2, Hence only 2 records are returned

When should we use the LIMIT keyword?

Let’s suppose that we are developing the application that runs on top of myflixdb. Our system designer have asked us to limit the number of records displayed on a page to say 20 records per page to counter slow load times. How do we go about implementing the system that meets such user requirements? The LIMIT keyword comes in handy in such situations. We would be able to limit the results returned from a query to 20 records only per page.

 

Summary

  • The LIMIT keyword of is used to limit the number of rows returned from a result set.
  • The LIMIT number can be any number from zero (0) going upwards. When zero (0) is specified as the limit, no rows are returned from the result set.
  • The OFF SET value allows us to specify which row to start from retrieving data.
  • It can be used in conjunction with the SELECT, UPDATE OR DELETE commands LIMIT keyword syntax.

 

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!

 

 

PostgreSQL tutorial for Beginners – PostgreSQL – LIMIT Clause

Beginners Guide to SQL – SQL TOP / MySQL LIMIT Clause

Python Built-in Methods – Python List index() Method