MariaDB: SELECT LIMIT Statement

This MariaDB tutorial explains how to use the SELECT LIMIT statement in MariaDB with syntax and examples.

Description

The MariaDB SELECT LIMIT statement is used to retrieve records from one or more tables in MariaDB and limit the number of records returned based on a limit value.

Syntax

The syntax for the SELECT LIMIT statement in MariaDB is:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT row_count;

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

ORDER BY expression

Optional. It is used in the SELECT LIMIT statement so that you can order the results and target those records that you wish to return.

LIMIT row_count

Specifies a limited number of rows in the result set to be returned based on row_count. For example, LIMIT 10 would return the first 10 rows matching the SELECT criteria. This is where sort order matters so be sure to use an ORDER BY clause appropriately.

Example - Using LIMIT keyword

Let's look at how to use a SELECT statement with a LIMIT clause in MariaDB.

For example:

SELECT site_id, site_name, host_name
FROM sites
WHERE site_name = 'AODBA.com'
ORDER BY site_id DESC
LIMIT 4;

This MariaDB SELECT LIMIT example would select the first 4 records from the sites table where the site_name is 'AODBA.com'. Note that the results are sorted by site_id in descending order so this means that the 4 largest site_id values will be returned by the SELECT LIMIT statement.

If there are other records in the sites table that have a site_name of 'AODBA.com', they will not be returned by the SELECT LIMIT statement in MariaDB.

If we wanted to select the 4 smallest site_id values instead of the largest, we could change the sort order as follows:

SELECT site_id, site_name, host_name
FROM sites
WHERE site_name = 'AODBA.com'
ORDER BY site_id ASC
LIMIT 4;

Now the results would be sorted by site_id in ascending order, so the first 4 smallest site_id records that have a site_name of 'AODBA.com' would be returned by this SELECT LIMIT statement. No other records would be returned by this query.