MariaDB ROWNUM() Explained

In MariaDB, ROWNUM() is a built-in function that returns the current number of accepted rows in the current context. Its main purpose is to emulate the ROWNUM pseudo column in Oracle.

ROWNUM() can be used in a way that has a similar effect to the LIMIT clause – to limit the number of results returned by a query.

When in Oracle mode, it can be called as ROWNUM (i.e. without the parentheses).

The ROWNUM() function is supported from MariaDB 10.6.1.

Syntax

The syntax goes like this:

ROWNUM()

No arguments are required, or accepted.

When in Oracle mode, it can be called without the parentheses, like this:

ROWNUM

Using this syntax mimics the ROWNUM pseudo column in Oracle.

Example

Let’s run a query that returns all rows from a table called Pets:

SELECT 
    ROWNUM(),
    PetId,
    PetName
FROM Pets;

Result:

+----------+-------+---------+
| ROWNUM() | PetId | PetName |
+----------+-------+---------+
|        1 |     1 | Fluffy  |
|        2 |     2 | Fetch   |
|        3 |     3 | Scratch |
|        4 |     4 | Wag     |
|        5 |     5 | Tweet   |
|        6 |     6 | Fluffy  |
|        7 |     7 | Bark    |
|        8 |     8 | Meow    |
+----------+-------+---------+
8 rows in set (0.001 sec)

We can see that eight rows were returned.

The value returned by ROWNUM() function increments with each row. In this case, it happens to coincide with the values in the PetId column, but this is purely coincidental. The PetId column could’ve used any value, but the ROWNUM() would remain as it is here.

To illustrate what I mean, let’s refine the query to return less results:

SELECT 
    ROWNUM(),
    PetId,
    PetName
FROM Pets
WHERE PetId > 4;

Result:

+----------+-------+---------+
| ROWNUM() | PetId | PetName |
+----------+-------+---------+
|        1 |     5 | Tweet   |
|        2 |     6 | Fluffy  |
|        3 |     7 | Bark    |
|        4 |     8 | Meow    |
+----------+-------+---------+
4 rows in set (0.010 sec)

Limiting the Rows Returned

As mentioned, ROWNUM() can be used in a way that has a similar effect to the LIMIT clause – to limit the number of results returned by a query.

Here’s an example:

SELECT 
    ROWNUM(),
    PetId,
    PetName
FROM Pets
WHERE ROWNUM() <= 5;

Result:

+----------+-------+---------+
| ROWNUM() | PetId | PetName |
+----------+-------+---------+
|        1 |     1 | Fluffy  |
|        2 |     2 | Fetch   |
|        3 |     3 | Scratch |
|        4 |     4 | Wag     |
|        5 |     5 | Tweet   |
+----------+-------+---------+
5 rows in set (0.001 sec)

Here’s how we’d get the same effect using the LIMIT clause:

SELECT 
    ROWNUM(),
    PetId,
    PetName
FROM Pets
LIMIT 5;

Result:

+----------+-------+---------+
| ROWNUM() | PetId | PetName |
+----------+-------+---------+
|        1 |     1 | Fluffy  |
|        2 |     2 | Fetch   |
|        3 |     3 | Scratch |
|        4 |     4 | Wag     |
|        5 |     5 | Tweet   |
+----------+-------+---------+
5 rows in set (0.001 sec)

There is a difference between using LIMIT and ROWNUM() to limit the rows returned.

The main difference is that LIMIT works on the result set while ROWNUM works on the number of accepted rows (before any ORDER or GROUP BY clauses).

Here’s an example that demonstrates this difference:

SELECT 
    ROWNUM(),
    PetId,
    PetName
FROM Pets
WHERE ROWNUM() <= 5
ORDER BY PetId Desc;

Result:

+----------+-------+---------+
| ROWNUM() | PetId | PetName |
+----------+-------+---------+
|        5 |     5 | Tweet   |
|        4 |     4 | Wag     |
|        3 |     3 | Scratch |
|        2 |     2 | Fetch   |
|        1 |     1 | Fluffy  |
+----------+-------+---------+
5 rows in set (0.129 sec)

And here it is using the LIMIT clause:

SELECT 
    ROWNUM(),
    PetId,
    PetName
FROM Pets
ORDER BY PetId Desc
LIMIT 5;

Result:

+----------+-------+---------+
| ROWNUM() | PetId | PetName |
+----------+-------+---------+
|        8 |     8 | Meow    |
|        7 |     7 | Bark    |
|        6 |     6 | Fluffy  |
|        5 |     5 | Tweet   |
|        4 |     4 | Wag     |
+----------+-------+---------+
5 rows in set (0.000 sec)

Omitting the Parentheses

When running in Oracle mode, it’s possible to omit the parentheses. Doing this enables you to emulate the ROWNUM pseudo column in Oracle.

Here’s an example of switching to Oracle mode:

SET SQL_MODE='ORACLE';

Now we can run ROWNUM without parentheses:

SELECT 
    ROWNUM,
    PetId,
    PetName
FROM Pets;

Result:

+--------+-------+---------+
| ROWNUM | PetId | PetName |
+--------+-------+---------+
|      1 |     1 | Fluffy  |
|      2 |     2 | Fetch   |
|      3 |     3 | Scratch |
|      4 |     4 | Wag     |
|      5 |     5 | Tweet   |
|      6 |     6 | Fluffy  |
|      7 |     7 | Bark    |
|      8 |     8 | Meow    |
+--------+-------+---------+
8 rows in set (0.029 sec)

Note that this is only available when in Oracle mode. Using ROWNUM without parentheses when not in Oracle mode results in an error.

To demonstrate, let’s switch to default mode:

SET SQL_MODE=DEFAULT;

Now run the query again:

SELECT 
    ROWNUM,
    PetId,
    PetName
FROM Pets;

Result:

ERROR 1054 (42S22): Unknown column 'ROWNUM' in 'field list'

See the MariaDB documentation for some considerations around optimization and other factors.