MariaDB Limit

Summary: in this tutorial, you will learn how to use the MariaDB limit clause to specify the number of rows returned by a query.

Introduction to MariaDB limit clause

The limit clause allows you to specify the number of rows returned by a select statement.

Here is the syntax of the limit clause:

select select_list
from tale_name
order by sort_expression
limit n [offset m];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• n is the number of rows to return
• m is the number of rows to skip before returning the n rows.

The select statement returns rows in an unspecified order, therefore, the limit clause without an order by clause will return a result of unspecified rows.

It is a good practice to always use the limit clause with an order by clause to make returned rows predictable.

MariaDB provides an alternative syntax of the limit clause as follows:

LIMIT m, n;
Code language: SQL (Structured Query Language) (sql)

The syntax means skipping m rows and returning the next n rows.

MariaDB limit clause examples

We’ll use the countries table from the sample database to demonstrate the limit clause:

A) Using the MariaDB limit clause examples

The following statement returns all rows from the countries table sorted by the country name:

select 
    name
from 
    countries
order by 
    name;
Code language: SQL (Structured Query Language) (sql)

To select only the first 5 countries, you add the limit clause to the above query:

select 
    name
from 
    countries
order by 
    name
limit 5;
Code language: SQL (Structured Query Language) (sql)

To skip the first 5 countries and select the next 10 countries, you use the offset clause:

select 
    name
from 
    countries
order by 
    name
limit 10 offset 5;
Code language: SQL (Structured Query Language) (sql)

The following query uses the alternative limit syntax:

select 
    name
from 
    countries
order by 
    name
limit 5, 10;
Code language: SQL (Structured Query Language) (sql)

B) Using MariaDB limit clause to get the top-N rows example

The limit clause is very handy to make the top-N report e.g., the top 10 largest countries:

select
    name,
    area
from
    countries
order by 
    area desc
limit 10;
Code language: SQL (Structured Query Language) (sql)

In this example:

• First, sort the countries by areas from large to small.
• Second, select only the first 10 countries.

C) Using the MariaDB limit clause to select the nth row example

You can use the limit clause to get the nth row. For example, to get the 2nd largest country, you use the following statement:

select
    name,
    area
from
    countries
order by 
    area, 
    name
limit 1,1;
Code language: SQL (Structured Query Language) (sql)

First, the query sorts countries by areas. Then, it skips the first row and picks the second one, which is the second largest country.

Note that if two or more 2nd largest countries have the same area, the query can only return the first one.

In this tutorial, you have learned how to use the MariaDB limit clause to specify the number of rows returned by a query.