Limit in MySQL

Syntax

The fundamental syntax for the LIMIT in MySQL is:

• column_name(s): This is the name or names of the column(s) you want to retrieve data from. You can select multiple columns by separating them with a comma.
• table_name: This is the name of the table from which you want to retrieve data.
• condition: This is the criteria or filter that is applied to the data retrieved from the table.
• LIMIT: This keyword is used to limit the number of rows returned by the query.

Introduction

In a SELECT statement, the LIMIT clause is used to restrict the number of rows to be retrieved. This clause can take either one or two arguments, both of which must be non-negative integers.

If you're working with a table that contains a large number of rows, or if you only need to retrieve the most recently inserted data, it's crucial to use the MySQL Limit clause with your SELECT statement. This approach enhances the query's performance and prevents system crashes that may occur when dealing with extensive amounts of data. In short, utilizing the LIMIT` in MySQL query is an effective way to avoid returning all rows when it's unnecessary.

MYSQL LIMIT with ORDER BY clause

By combining the LIMIT keyword with the ORDER BY clause, it's possible to both control the order in which records are retrieved and limit the number of records that are returned.

Ascending or descending order of one or more columns can be achieved using the ORDER BY clause, which is used to arrange the result set. To utilize the syntax for combining LIMIT with ORDER BY, use the following:

Here,

• col1, col2... are the columns that are to be chosen and arranged in order.
• tablename is the table name that contains the data to retrieve.
• ORDER BY col1, col2, ... are the columns to sort the data by. If the sorting order is not specified, it defaults to ascending. To sort in descending order, add the "DESC" keyword after the column name.
• LIMIT offset, count, The "offset" parameter determines the number of records to skip, while the "count" parameter specifies the maximum number of records to retrieve.

Let's take an example, to retrieve the first 4 records from the "cars" table, sorted by the "car_id" column in descending order, use the following query:

This query would retrieve the 4 cars with the highest "car_id" values, which would be the last 4 records when sorted in descending order.

MySQL LIMIT Clause Examples

Using MySQL LIMIT to get the Highest or Lowest rows

To fetch multiple rows with either the highest or lowest values, adjust the LIMIT clause as needed. Let's understand with the example given below:

To retrieve the top 3 rows with the highest values from the above "Emp" table, use the following code:

Output:

So after executing this query the name of persons with top 3 EmpId is displayed.

Using LIMIT in MySQL for Pagination

When presenting data on a screen, it's common to split the rows into pages with a predefined maximum number of rows per page, such as 10 or 20.

To use this pagination with the help of the LIMIT clause, you have to define both the number of records to retrieve per page and the initial offset. The offset refers to the position of the first record on the current page and is calculated based on the current page number and the number of records per page.

Below is a sample query that retrieves 10 records per page and displays the results for page 2:

In the given example, the LIMIT clause is set to restrict the number of results to 10. Additionally, the OFFSET clause determines the initial offset position by utilizing the formula (page_number - 1) * records_per_page. In this case, for page 2, the starting offset is (2 - 1) * 10 = 10.

To simplify the process of calculating the initial offset, you can utilize the shortened syntax that is "LIMIT , ". In this format, corresponds to the starting offset, while refers to the number of records to retrieve. Consequently, the earlier query example would be revised as follows:

Executing this query will result in the retrieval of 10 records beginning from the 11th record, which represents the second page with 10 records per page.

Let's understand with the example given below:

For retrieving the name of 2 persons starting with the 4rth name from the "Emp" table given above, we will use the following query:

Output:

Using MySQL LIMIT to get the nth Highest or Lowest Value

To obtain the value with the nth highest or lowest ranking, the following LIMIT clause can be employed:

In the given query above, the col_name signifies the name of the column from which you intend to retrieve the nth highest value, while n represents the position of the desired value.

The ORDER BY clause is responsible for sorting the column in descending order, resulting in the highest value being placed at the top. Furthermore, the LIMIT clause retrieves one row starting from the (n-1)th row, signifying the nth highest value.

Let's understand with the example given below:

For retrieving the name of the person with 3rd largest EmpId from the "Emp" table given above, we will use the following query:

Output:

After executing the query, the person with 3rd largest EmpId i.e. "Satyam" here is displayed as an output.

To obtain the value with the nth lowest ranking, the column can be sorted in ascending order and the LIMIT clause can be used to retrieve the nth row. Below is an example:

In the query given above, the col_name refers to the column from which the nth lowest value is retrieved, and n indicates the position of the value to be retrieved.

In this query, the ORDER BY clause sorts the column in ascending order, and the lowest value is retrieved first. The LIMIT clause retrieves one row, starting from the n-1th row, which represents the nth lowest value.

It should be noted that if multiple rows have the same value, using LIMIT to retrieve the nth highest or lowest value may not always yield an accurate result because it depends on how the ORDER BY clause sorts those rows.

MySQL LIMIT & DISTINCT clauses

By combining the LIMIT and DISTINCT clauses in MySQL, it is possible to restrict the number of unique rows that are retrieved from a query. Here's an example:

This particular query utilizes the DISTINCT clause to guarantee that unique values of "col_name" are fetched. Additionally, the LIMIT clause restricts the outcome to a maximum of 5 unique rows.

It's essential to keep in mind that the LIMIT clause takes effect after the DISTINCT clause has been applied. Consequently, it's possible that the result set may not always comprise exactly five rows. If there are fewer than five unique values of "col_name," the query will return all of them.

When Should We Use the LIMIT Keyword?

MySQL queries often use the LIMIT keyword to confine the number of rows retrieved from a query. It's especially advantageous when dealing with massive datasets and only requires a fraction of the data. Consequently, you should use the LIMIT keyword to restrict the number of rows returned by a query or to enable pagination on a website or application.

Furthermore, the LIMIT keyword can be combined with other clauses such as ORDER BY or DISTINCT to further refine the outcome of a query.