MariaDB ROWNUM() Function: Version, Behavior, and Examples

MariaDB ROWNUM() returns the number of rows accepted so far in the current query context. MariaDB added it in version 10.6.1. In Oracle SQL mode, you can write ROWNUM without parentheses. See the official ROWNUM reference.

ROWNUM() is not the window function ROW_NUMBER(). Use ROW_NUMBER() with OVER (ORDER BY ...) when you need an ordered row number or a top-N result per group. For ordinary MariaDB queries, prefer LIMIT, which applies to the result set and is more predictable.

Syntax

ROWNUM()

The function has no arguments. It counts accepted rows before ORDER BY or GROUP BY in its query context, so its result can depend on the order MariaDB reads rows. Adding an index or changing the query plan can change which rows are accepted first.

Limit rows with ROWNUM()

This returns up to five rows, but does not define which five rows are selected:

SELECT film_id, title
FROM film
WHERE ROWNUM() <= 5;

For a predictable result, sort in a subquery and apply ROWNUM() in the outer query:

SELECT film_id, title
FROM (
    SELECT film_id, title
    FROM film
    ORDER BY film_id
) AS ordered_films
WHERE ROWNUM() <= 5
ORDER BY film_id;

For MariaDB application queries, the simpler native form is usually preferable:

SELECT film_id, title
FROM film
ORDER BY film_id
LIMIT 5;

To skip rows, use LIMIT with OFFSET instead of trying to make ROWNUM() start at a later number:

SELECT film_id, title
FROM film
ORDER BY film_id
LIMIT 5 OFFSET 5;

The film examples use the Sakila sample database. For details about ordered numbering and partitioned rankings, see the ROW_NUMBER() guide.