MySQL SELECT LIMIT Statement

Overview

In this tutorial, we will learn MySQL SELECT LIMIT Statement with examples. This is an MySQL exclusive option to limit the query output. The LIMIT clause is used to restrict the number of rows retrieved by the query from the MySQL database.

Syntax

The syntax of the LIMIT clause is:

LIMIT row_count

LIMIT skip_count, row_count

This clause is useful when the table contains many rows and we want to view only few of the rows. Note that the LIMIT clause is optional.

For example,

SELECT [DISTINCT] columns_in_result
FROM table_name(s)
WHERE condition(s)
ORDER BY column_name sort_order
LIMIT [skip_count,] row_count;

Example

We can add the LIMIT clause to limit the number of rows in the query.We will use the clause on the Country table from the world database.

mysql> SELECT Name, Capital FROM Country;

The above query will retrieve all the rows in the table. Let’s use the LIMIT clause to retrieve few rows. For example, to limit the number of rows in the query result to 10 rows.

mysql> SELECT Name, Capital FROM Country LIMIT 10;

MySQL SELECT Command

Using LIMIT skip count

The LIMIT clause can contain the skip_count to skip the results from the beginning and then display the rows specified by the row_count.

For example, the Country table has 239 rows.

mysql> SELECT COUNT(*) FROM Country;
+———-+
| COUNT(*) |
+———-+
| 239 |
+———-+
1 row in set (0.01 sec)

We can use the skip count to skip the rows of the result. For example, to display the last result, we can specify the skip count as 238 for the Country table.

LIMIT Skip Count Clause

We can also use LIMIT clause with ORDER BY clause. MySQL server applies the ORDER BY clause first and then limits the result set with the LIMIT clause.

Let’s use the LIMIT clause to find the largest payment Amount from the Payment table in the sakila database. We will use the ORDER BY clause to sort the Amount column in descending order. The first row has the largest Amount value. We will limit the result to the first row with LIMIT 1.

mysql> SELECT Payment_id, Amount
-> FROM Payment
-> ORDER BY Amount DESC
-> LIMIT 1;

+————+——–+
| Payment_id | Amount |
+————+——–+
| 342 | 11.99 |
+————+——–+
1 row in set (0.01 sec)

LIMIT ORDER BY Clause Amount