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 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 – As you can see from the above screenshot, only two members have been returned. 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. Executing the above script gives us the results shown below 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 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. Executing the above script in MySQL workbench against the myflixdb gives the following results.

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