MariaDB ROWNUM() Function

In MariaDB, ROWNUM() is a built-in function that returns the row number for each row in the current query.

The MariaDB ROWNUM() function are very similar to ROWNUM pseudo columns in Oracle. In Oracle mode, you can omit the parentheses.

In ORACLE, you can use ROWNUM to implement MariaDB LIMIT.

MariaDB ROWNUM() Syntax

Here is the syntax of the MariaDB ROWNUM() function:

ROW_COUNT()

Parameters

The MariaDB ROWNUM() function do not require any parameters.

Return value

The MariaDB ROWNUM() function returns the row number for each row in the current query.

MariaDB ROWNUM() Examples

The following example uses the film table from the Sakila sample database.

To query the first 5 rows from the film table, use the following statement:

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

Output:

+----------+---------+------------------+
| ROWNUM() | film_id | title            |
+----------+---------+------------------+
|        1 |       1 | ACADEMY DINOSAUR |
|        2 |       2 | ACE GOLDFINGER   |
|        3 |       3 | ADAPTATION HOLES |
|        4 |       4 | AFFAIR PREJUDICE |
|        5 |       5 | AFRICAN EGG      |
+----------+---------+------------------+

This is equivalent to the following statement with the LIMIT clause:

SELECT ROWNUM(), film_id, title
FROM film
LIMIT 5;

To query rows with rownum from 6 to 10 from the film table, use the following statement:

SELECT * FROM (
  SELECT ROWNUM() row_num, film_id, title
  FROM film
) t
WHERE row_num >= 6 AND row_num <=10;

Output:

+---------+---------+------------------+
| row_num | film_id | title            |
+---------+---------+------------------+
|       6 |       6 | AGENT TRUMAN     |
|       7 |       7 | AIRPLANE SIERRA  |
|       8 |       8 | AIRPORT POLLOCK  |
|       9 |       9 | ALABAMA DEVIL    |
|      10 |      10 | ALADDIN CALENDAR |
+---------+---------+------------------+

Or you can use the following statement:

SELECT * FROM(
  SELECT ROWNUM() row_num, film_id, title
  FROM film
  WHERE ROWNUM() <= 10
  ) t
WHERE row_num >= 6;

This is equivalent to the following statement with the LIMIT clause:

SELECT ROWNUM(), film_id, title
FROM film
LIMIT 5, 5;

Note that an empty result set will be returned if you use the following statement:

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

or

SELECT ROWNUM(), film_id, title
FROM film
WHERE ROWNUM() >= 6 AND ROWNUM() <= 10;

Conclusion

In MariaDB, ROWNUM() is a built-in function that returns the row number for each row in the current query.