ROWNUM()

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.6.0

From version 10.6.0, MariaDB supports the ROWNUM() function.

Syntax

ROWNUM()

In Oracle mode one can use just ROWNUM.

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 differnt 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 only recommend way to use ROWNUM to limit the number of returned rows and get predictable results is having the query in a sub query 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 context:

Used in other context 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 considerations

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

  • When the optimizer is finding rows in different order (because of different storage methods or optimization). This may also happen in Oracle if one adds a 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 contex, not the callers context.

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.