An Introduction to the LIMIT Clause in MySQL

SQL or Structured Query Language is a set of instructions used for interacting with relational databases. As a matter of fact, it is the only language that is understood by most of databases. SQL statements are used basically for performing tasks such as updating data in a database or retrieving data from the database. The common RDBM systems that use SQL are Microsoft SQL Server, Oracle, Access, Ingres, and Sybase. SQL is composed of three major components called the Data Definition Language, Data Manipulation Language and Data Control Language. RDBMS consists of one or more objects known as tables where data or information is stored. You can use select statements along with specified conditions to retrieve desired data from your tables. By default, all records that satisfy those conditions are returned. However, you might just want a subset of records. In SQL, this can be accomplished using the LIMIT statement.

Learn SQL Programming from scratch. Take an online class at Udemy.com

Use of the Limit keyword in MySQL:

Limit is used in MySQL Select statement to specifically limit query results to those that lie within a particular range. It can accept one or two arguments and the value of those arguments should be either zero or positive integer constants.

LIMIT clause with single argument:

The general syntax for Limit keyword is as follows:

[SQL statement]
LIMIT[N]

Here, N is the number of records that needs to be retrieved. Note that with the limit keyword, the order by clause is usually included in an SQL query. If a single argument is used, it will specify the maximum number of rows to return from the start of the result set. Without the order by clause, the fetched results are dependent on the database defaults.

Example:

Let’s create a table named “store_table” for this example.

Store_table

For instance, if we want to show the highest three sales made in “Store_table”,then we can write the query as:

SELECT store_name, sales
FROM store_table
ORDER BY sales DESC
LIMIT 3

This will show the result such as the following:

Learn SQL Programming by taking a course at Udemy.com

MySQL limit for getting highest and lowest value:

In order to show the highest four sales from the “store_table,” we will use the following query:

SELECT Store_num,Store_name,Sales
FROM Store_table
ORDER BY Sales DESC
LIMIT 4;

LIMIT clause with two arguments:

Sometimes, in certain scenarios, you might not want to display the rows from the beginning and instead display records that start from a particular row. In these types of situations, you can use two arguments in the LIMIT clause. You can use the LIMIT to retrieve the first A results or extract a range from A- B results. The statement is paraphrased as Limit A, B and included at the last of the query.

General syntax:

LIMIT offset, count

Offset: It specifies the offset of the first row to return. Note that the offset for the first row is 0 and not 1.

Count: Used for specifying the number of rows to return.

Example:

If you want to display only three rows from the “Store_table” table with the result starting from the second row, you can use the following code:

SELECT * from store_table
LIMIT 1, 3

The above query will display row 2,3,4.

SELECT * from store_table
LIMIT 0, 4

This query will show the first four results from the table “store_table”.

MySQL LIMIT for getting N highest values:

What if you only want to display the row with the second highest sale? The LIMIT clause with ORDER BY is used for this scenario and the code is:

SELECT Store_name, Sales
FROM Store_table
ORDER BY Sales DESC
LIMIT 1,1

The above code will select only Store_name and Sales column from the table “Store_table” and ORDER BY will then sort the result in descending order, according to the sales value. The LIMIT clause will display only the second row, which will be the second highest sale’s row. The result will look like:

SQL collections are arrays in memory that leads to massive collections causing a detrimental effect on system performance, because of the enormous amount of memory they require. In many situations, it may be feasible to split the processing data into chunks for making the code more user- friendly. If you have a thousand rows in a database table, but don’t want all of them to display, then you can use the LIMIT clause. The LIMIT keyword is quite similar to the TOP clause in Microsoft SQL server. Using LIMIT in SQL statements makes it easy to code multiple page results or do pagination in MySQL. The usual approach is to select all records and then filter them on the application server tier instead of doing it directly on the database. By using the LIMIT clause, you can significantly improve the performance of your database.

Where to use the LIMIT keyword:

Let’s assume that you are supposed to develop a web application and the system designer has asked to limit the number of records being displayed on a single page to 10. How do you implement a system that exactly meets user requirements? The LIMIT clause comes handy in these situations. You will be able to limit results that are returned from SQL queries to 10 records per page easily.

Learn more about SQL Programming by taking a course at Udemy.com

Conclusion:

SQL developers are in great demand since they work with one of the most popular and oldest programming languages ever. There are only a few places where you can implement performance gains within an application, only if your code is well written. Making minute changes such as including the LIMIT keyword in your SQL queries can have tremendous performance improvements. Also, you can improve the visibility and friendliness of any page on an application by limiting the number of records displayed.