limit in sql

What is the LIMIT keyword?
The limit keyword is used to limit the number of rows returned from a query.

Can be used in conjunction with SELECT, UPDATE OR DELETE LIMIT commands keyword syntax

The syntax for the LIMIT keyword is as follows

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

ВОТ

"SELECT { fieldname(s) | *} FROM tableName(s)" is a SELECT statement containing the fields we would like to return in our query.
"[WHERE condition]" is optional, but can be used to specify a filter on the result set if present.
"LIMIT N" is a keyword, and N is any number that starts with 0, where 0, as the limit, returns no records in the query. If you enter a number, say 5, you get five entries. If the records in the specified table are less than N, then all records from the queried table are returned in the result set.

Let's look at an example −

SELECT *  FROM members LIMIT 2;

As you can see in the screenshot above, only two members were returned.

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

Let's say we want to get a list of the first 10 registered users from the Myflix database. We would use the following script to achieve this.

SELECT * FROM members LIMIT 10;

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

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 we want to get a limited number of members starting from the middle of the rows, we can use the LIMIT keyword along with an offset value to achieve this. The script shown below gets the data starting from the second row and limits the results to 2.

SELECT * FROM `members` LIMIT 1, 2;

Running the above script in MySQL Workbench against myflixdb gives the following results.

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 assume we are developing an application that runs on top of myflixdb. Our system designer asked us to limit the number of entries displayed per page to 20 entries per page to counter slow load times. How do we implement a system that meets such user requirements? The LIMIT keyword comes in handy in such situations. We will be able to limit the results returned by the query to 20 records per page only.

Summary

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