ROWNUM()

This page is part of MariaDB's Documentation.

The parent of this page is: Functions for MariaDB Enterprise Server

Overview

In 23.08 ES, 23.07 ES, 10.6 ES, 10.6 CS:: Returns the row number of the row's data as extracted from the DB, beginning with row 1.

In 23.08 ES, 23.07 ES, 10.6 ES:: Returns the row number of the row's data as extracted from the DB, beginning with row 1.

In 10.5 ES, 10.5 CS, 10.4 ES, 10.4 CS, 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS:: Not present

In 10.5 ES, 10.4 ES:: Not present

USAGE

ROWNUM()

DETAILS

ROWNUM() returns a number for each row extracted from a query, beginning with row 1.
ROWNUM() returns a 1 when called outside of a row context.
When ROWNUM() is called in a query, ROWNUM() returns the order in which the row was selected from a table or joined rows. When MariaDB Server selects the first row, ROWNUM() will return 1. When MariaDB Server selects the second row, ROWNUM() will return 2. The ROWNUM() values continue increasing by 1 for each selected row.
Rows in the result set are not always sorted by the row number values, because the order of results can vary depending on the storage engines used for the tables, the indexes used to read data, and the optimizations used to execute the query. To ensure that the rows in the result set are sorted by the row number values, you can use ORDER BY ROWNUM().
When the sql_mode system variable is set to ORACLE, the ROWNUM pseudocolumn is supported when another column does not use the name, and it is populated with values from the ROWNUM() function.

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

Non-row Call

When called outside of a row context, ROWNUM() always returns a 1:

SELECT ROWNUM();

+----------+
| ROWNUM() |
+----------+
|        1 |
+----------+

Example Schema

Most of the examples use this data:

CREATE TABLE test_values (
   a INT,
   b INT
);

INSERT INTO test_values VALUES
  (1,3), (1,5), (8,2), (5,7), (5,6),
  (10,1), (6,4), (3,9), (3,9), (7,2),
  (7,5), (2,6), (9,10), (9,5), (4,8);

Row Number Values from a Table

When ROWNUM() is called in a query, ROWNUM() returns the order in which the row was selected from a table or joined rows. When MariaDB Server selects the first row, ROWNUM() will return 1. When MariaDB Server selects the second row, ROWNUM() will return 2. The ROWNUM() values continue increasing by 1 for each selected row.

Rows in the result set are not always sorted by the row number values, because the order of results can vary depending on the storage engines used for the tables, the indexes used to read data, and the optimizations used to execute the query. To ensure that the rows in the result set are sorted by the row number values, you can use ORDER BY ROWNUM().

For example, the following query returns the first 7 rows sorted by the row number values:

SELECT *, ROWNUM()
FROM test_values
WHERE ROWNUM() <= 7
ORDER BY ROWNUM();

+------+------+----------+
| a    | b    | ROWNUM() |
+------+------+----------+
|    1 |    3 |        1 |
|    1 |    5 |        2 |
|    8 |    2 |        3 |
|    5 |    7 |        4 |
|    5 |    6 |        5 |
|   10 |    1 |        6 |
|    6 |    4 |        7 |
+------+------+----------+

The same results could be obtained using LIMIT:

SELECT *, ROWNUM()
FROM test_values
ORDER BY ROWNUM()
LIMIT 7;

+------+------+----------+
| a    | b    | ROWNUM() |
+------+------+----------+
|    1 |    3 |        1 |
|    1 |    5 |        2 |
|    8 |    2 |        3 |
|    5 |    7 |        4 |
|    5 |    6 |        5 |
|   10 |    1 |        6 |
|    6 |    4 |        7 |
+------+------+----------+

Alternate Order

ROWNUM() can be used to enumerate rows in an alternate order using a subquery.

When ROWNUM() is called with ORDER BY in a flat SELECT query, the rows are enumerated in the order they are read, so the row numbers are unaffected by the ORDER BY in this scenario:

SELECT *, ROWNUM()
FROM test_values
ORDER BY a, b, ROWNUM();

+------+------+----------+
| a    | b    | ROWNUM() |
+------+------+----------+
|    1 |    3 |        1 |
|    1 |    5 |        2 |
|    2 |    6 |       12 |
|    3 |    9 |        8 |
|    3 |    9 |        9 |
|    4 |    8 |       15 |
|    5 |    6 |        5 |
|    5 |    7 |        4 |
|    6 |    4 |        7 |
|    7 |    2 |       10 |
|    7 |    5 |       11 |
|    8 |    2 |        3 |
|    9 |    5 |       14 |
|    9 |   10 |       13 |
|   10 |    1 |        6 |
+------+------+----------+

When ROWNUM() is called in an outer SELECT query that has a subquery with an ORDER BY, the rows are enumerated in the order they appear in the subquery, so the row numbers are affected by the ORDER BY in this scenario:

SELECT *, ROWNUM()
FROM (
   SELECT *
   FROM test_values
   ORDER BY a, b
) t;

+------+------+----------+
| a    | b    | ROWNUM() |
+------+------+----------+
|    1 |    3 |        1 |
|    1 |    5 |        2 |
|    2 |    6 |        3 |
|    3 |    9 |        4 |
|    3 |    9 |        5 |
|    4 |    8 |        6 |
|    5 |    6 |        7 |
|    5 |    7 |        8 |
|    6 |    4 |        9 |
|    7 |    2 |       10 |
|    7 |    5 |       11 |
|    8 |    2 |       12 |
|    9 |    5 |       13 |
|    9 |   10 |       14 |
|   10 |    1 |       15 |
+------+------+----------+

Select Specific Row Numbers

Specific row numbers can be selected with ROWNUM() by aliasing the result in a subquery or CTE.

When you try to select a specific ROWNUM() value in a flat SELECT query, the query returns an empty result set for all row number values except 1:

SELECT *, ROWNUM()
FROM test_values
WHERE ROWNUM() IN (1, 7);

+------+------+----------+
| a    | b    | ROWNUM() |
+------+------+----------+
|    1 |    3 |        1 |
+------+------+----------+

When you try to select a specific ROWNUM() value with an alias in a flat SELECT query, the query will raise an error with the ER_BAD_FIELD_ERROR error code:

SELECT *, ROWNUM() AS row_num
FROM test_values
WHERE row_num IN (1, 7);

ERROR 1054 (42S22): Unknown column 'row_num' in 'where clause'

When you try to select a specific ROWNUM() value with a subquery that uses an alias, the query returns the proper rows:

SELECT * FROM (
  SELECT *, ROWNUM() AS row_num
  FROM test_values
  ORDER BY a
) t
WHERE row_num IN (1, 7);

+------+------+---------+
| a    | b    | row_num |
+------+------+---------+
|    1 |    3 |       1 |
|    6 |    4 |       7 |
+------+------+---------+

When you try to select a specific ROWNUM() value with a CTE that uses an alias, the query returns the proper rows:

WITH t AS (
  SELECT *, ROWNUM() AS row_num
  FROM test_values
)
SELECT *
FROM t
WHERE row_num IN (1, 7);

+------+------+---------+
| a    | b    | row_num |
+------+------+---------+
|    1 |    3 |       1 |
|    6 |    4 |       7 |
+------+------+---------+

ERROR HANDLING

FEATURE INTERACTION

ORACLE Compatibility

When the sql_mode system variable is set to ORACLE, MariaDB Server supports SQL/PL, a compatible subset of Oracle PL/SQL.

When SQL/PL is enabled, Oracle's ROWNUM pseudo-column is supported when another column does not use the name. The ROWNUM pseudo-column is populated with values from the ROWNUM() function.

SET sql_mode=CONCAT(@@sql_mode, ',ORACLE');

SELECT *, rownum
FROM test_values
ORDER BY rownum;

+------+------+--------+
| a    | b    | rownum |
+------+------+--------+
|    1 |    3 |      1 |
|    1 |    5 |      2 |
|    8 |    2 |      3 |
|    5 |    7 |      4 |
|    5 |    6 |      5 |
|   10 |    1 |      6 |
|    6 |    4 |      7 |
|    3 |    9 |      8 |
|    3 |    9 |      9 |
|    7 |    2 |     10 |
|    7 |    5 |     11 |
|    2 |    6 |     12 |
|    9 |   10 |     13 |
|    9 |    5 |     14 |
|    4 |    8 |     15 |
+------+------+--------+

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series	History
23.08 Enterprise	Present starting in MariaDB Enterprise Server 23.08.0.
23.07 Enterprise	Present starting in MariaDB Enterprise Server 23.07.0.
10.6 Enterprise	Added in MariaDB Enterprise Server 10.6.4-1.
10.6 Community	Added in MariaDB Community Server 10.6.1.
10.5 Enterprise	Not present.
10.5 Community	Not present.
10.4 Enterprise	Not present.
10.4 Community	Not present.
10.3 Enterprise	Not present.
10.3 Community	Not present.
10.2 Enterprise	Not present.
10.2 Community	Not present.

Release Series	History
23.08 Enterprise	Present starting in MariaDB Enterprise Server 23.08.0.
23.07 Enterprise	Present starting in MariaDB Enterprise Server 23.07.0.
10.6 Enterprise	Added in MariaDB Enterprise Server 10.6.4-1.
10.5 Enterprise	Not present.
10.4 Enterprise	Not present.

EXTERNAL REFERENCES

Additional information on this topic may be found in the MariaDB Public Knowledge Base.