MySQL LIMIT & OFFSET with Examples

Details

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

Let's look at an example -

SELECT *  FROM members LIMIT 2;


 		 
membership_ numberfull_ namesgenderdate_of _birthdate_of _registrationphysical_ addresspostal_ addresscontact_ numberemailcredit_ card_ number

		 
1Janet JonesFemale21-07-1980NULLFirst Street Plot No 4Private Bag0759 253 542This email address is being protected from spambots. You need JavaScript enabled to view it.NULL

		 
2Janet Smith JonesFemale23-06-1980NULLMelrose 123NULLNULLThis email address is being protected from spambots. You need JavaScript enabled to view it.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_ numberfull_ namesgenderdate_of _birthdate_of _registrationphysical_ addresspostal_ addresscontact_ numberemailcredit_ card_ number

		 
1Janet JonesFemale21-07-1980NULLFirst Street Plot No 4Private Bag0759 253 542This email address is being protected from spambots. You need JavaScript enabled to view it.NULL

		 
2Janet Smith JonesFemale23-06-1980NULLMelrose 123NULLNULLThis email address is being protected from spambots. You need JavaScript enabled to view it.NULL

		 
3Robert PhilMale12-07-1989NULL3rd Street 34NULL12345This email address is being protected from spambots. You need JavaScript enabled to view it.NULL

		 
4Gloria WilliamsFemale14-02-1984NULL2nd Street 23NULLNULLNULLNULL

		 
5Leonard HofstadterMaleNULLNULLWoodcrestNULL845738767NULLNULL

		 
6Sheldon CooperMaleNULLNULLWoodcrestNULL976736763NULLNULL

		 
7Rajesh KoothrappaliMaleNULLNULLWoodcrestNULL938867763NULLNULL

		 
8Leslie WinkleMale14-02-1984NULLWoodcrestNULL987636553NULLNULL

		 
9Howard WolowitzMale24-08-1981NULLSouthParkP.O. Box 4563987786553lwolowitz[at]email.meNULL

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_ numberfull_ namesgenderdate_of _birthdate_of _registrationphysical_ addresspostal_ addresscontact_ numberemailcredit_ card_ number

	 
2Janet Smith JonesFemale23-06-1980NULLMelrose 123NULLNULLThis email address is being protected from spambots. You need JavaScript enabled to view it.NULL

	 
3Robert PhilMale12-07-1989NULL3rd Street 34NULL12345This email address is being protected from spambots. You need JavaScript enabled to view it.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

 

YOU MIGHT LIKE: