MariaDB starting with 10.6.1
----------------------------
From MariaDB 10.6.1, the ROWNUM() function is supported.

Syntax
------

ROWNUM()

In Oracle mode one can just use ROWNUM, without the parentheses.

Description
-----------

ROWNUM() returns the current number of accepted rows in the current context.
It main purpose is to emulate the ROWNUM pseudo column in Oracle. For MariaDB
native applications, we recommend the usage of LIMIT, as it is easier to use
and gives more predictable results than the usage of ROWNUM().

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

The following queries will return the same results:

SELECT * from t1 LIMIT 10;
SELECT * from t1 WHERE ROWNUM() <= 10;

While the following may return different results based on in which orders the
rows are found:

SELECT * from t1 ORDER BY a LIMIT 10;
SELECT * from t1 ORDER BY a WHERE ROWNUM() <= 10;

The recommended way to use ROWNUM to limit the number of returned rows and get
predictable results is to have the query in a subquery and test for ROWNUM()
in the outer query:

SELECT * FROM (select * from t1 ORDER BY a) WHERE ROWNUM() <= 10;

ROWNUM() can be used in the following contexts:

* SELECT
* INSERT
* UPDATE
* DELETE
* LOAD DATA INFILE

Used in other contexts, ROWNUM() will return 0.

Examples
--------

INSERT INTO t1 VALUES (1,ROWNUM()),(2,ROWNUM()),(3,ROWNUM());

INSERT INTO t1 VALUES (1),(2) returning a, ROWNUM();

UPDATE t1 SET row_num_column=ROWNUM();

DELETE FROM t1 WHERE a < 10 AND ROWNUM() < 2;

LOAD DATA INFILE 'filename' into table t1 fields terminated by ',' 
 lines terminated by "\r\n" (a,b) set c=ROWNUM();

Optimizations
-------------

In many cases where ROWNUM() is used, MariaDB will use the same optimizations
it uses with LIMIT.

LIMIT optimization is possible when using ROWNUM in the following manner:

* When one is in a top level WHERE clause comparing ROWNUM() with a numerical
constant using any of the following expressions:
ROWNUM() < number
ROWNUM() <= number
ROWNUM() = 1
ROWNUM() can be also be the right argument to the comparison function.

In the above cases, LIMIT optimization can be done in the following cases:

* For the current sub query when the ROWNUM comparison is done on the top
level:

SELECT * from t1 WHERE ROWNUM() <= 2 AND t1.a > 0

* For an inner sub query, when the upper level has only a ROWNUM() comparison
in the WHERE clause:

SELECT * from (select * from t1) as t WHERE ROWNUM() <= 2

Other Changes Related to ROWNUM
-------------------------------

When ROWNUM() is used anywhere in a query, the optimization to ignore ORDER BY
in subqueries are disabled.

This was done to get the following common Oracle query to work as expected:

select * from (select * from t1 order by a desc) as t where rownum() <= 2;

By default MariaDB ignores any ORDER BY in subqueries both because the SQL
standard defines results sets in subqueries to be un-ordered and because of
performance reasons (especially when using views in subqueries). See MDEV-3926
"Wrong result with GROUP BY ... WITH ROLLUP" for a discussion of this topic.

Other Considerations
--------------------

While MariaDB tries to emulate Oracle's usage of ROWNUM() as closely as
possible, there are cases where the result is different:

* When the optimizer finds rows in a different order (because of different
storage methods or optimization). This may also happen in Oracle if one adds
or deletes an index, in which case the rows may be found in a different order.

Note that usage of ROWNUM() in functions or stored procedures will use their
own context, not the caller's context.

URL: https://mariadb.com/kb/en/rownum/