The LIMIT Clause
This YouTube video was created by Steve Griffith.
Another way to limit the results returned from the database is use the LIMIT clause. For example, if we only want 5 rows to be returned. The LIMIT clause is followed by a number, which is the number of rows to be return OR in the case of the UPDATE or DELETE command the number of rows affected.
# Getting the 5 most recent movies
SELECT *
FROM `movies`
ORDER BY `year` DESC
LIMIT 5;
Pagination can be accomplished by inserting a second number after the LIMIT clause. This offset will be used to tell the database to start the row count a specified number of rows. For MySQL the syntax is LIMIT offset, row count. So, if we wanted to retrieve the third page of products it can be accomplished with the following:
# Getting the 5 most recent movies after the 10th movie
SELECT *
FROM `movies`
ORDER BY `year` DESC
LIMIT 10, 5;
Note
When using the LIMIT clause, it is important to use an ORDER BY clause to make sure that the rows in the returned are in a specified order.