MariaDB ROW_NUMBER(): Syntax, Ties and Examples

MariaDB ROW_NUMBER() is a window function that assigns a unique integer to each row, starting at 1. Use PARTITION BY to restart numbering for each group and ORDER BY to choose the numbering order. MariaDB added window functions in version 10.2; see the official ROW_NUMBER reference and MariaDB 10.2 release notes.

Do not confuse ROW_NUMBER() with MariaDB’s separate ROWNUM() function. ROW_NUMBER() is a window function for ordered numbering and per-group rankings; ROWNUM() counts accepted rows in a query context and does not define their order.

Syntax

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC | DESC], ...]
)

• PARTITION BY divides rows into groups. Numbering restarts at 1 for each group. If omitted, all rows belong to one partition.
• ORDER BY inside OVER determines which row gets each number. If the sort expressions can tie, add a unique tie-breaker such as a primary key to make the numbering repeatable.
• An ORDER BY outside OVER controls the order of the final result; it does not change the numbering rule.

Without an ORDER BY inside OVER, MariaDB does not guarantee which row receives each number.

Example table

The examples use this table, which includes two employees with the same salary:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(20) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO employees VALUES
    (1, 'Alice', 'Sales', 5000),
    (2, 'Bob', 'Sales', 5000),
    (3, 'Cara', 'Sales', 4000),
    (4, 'Dan', 'IT', 7000),
    (5, 'Eve', 'IT', 6000);

Number rows within each department

Partition by department and sort by salary. The id column breaks salary ties so the row numbers are deterministic:

SELECT
    id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC, id
    ) AS rn
FROM employees
ORDER BY department, rn;

+----+-------+------------+--------+----+
| id | name  | department | salary | rn |
+----+-------+------------+--------+----+
|  4 | Dan   | IT         |   7000 |  1 |
|  5 | Eve   | IT         |   6000 |  2 |
|  1 | Alice | Sales      |   5000 |  1 |
|  2 | Bob   | Sales      |   5000 |  2 |
|  3 | Cara  | Sales      |   4000 |  3 |
+----+-------+------------+--------+----+

Return the top two employees in each department

Calculate row numbers in a derived table, then filter the numbered rows in the outer query:

SELECT id, name, department, salary, rn
FROM (
    SELECT
        id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC, id
        ) AS rn
    FROM employees
) AS ranked
WHERE rn <= 2
ORDER BY department, rn;

This returns the two highest-paid employees in each department. Use a subquery or CTE when you need to filter by a window-function result.

How ROW_NUMBER handles ties

ROW_NUMBER() gives tied rows different numbers. In the example, Alice and Bob both earn 5000, but the id tie-breaker assigns them rows 3 and 4 in an overall ranking. If tied values should receive the same rank, use RANK() or DENSE_RANK() instead.

SELECT
    id,
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees
ORDER BY salary DESC, id;

+----+-------+--------+---------+----------+----------------+
| id | name  | salary | row_num | rank_num | dense_rank_num |
+----+-------+--------+---------+----------+----------------+
|  4 | Dan   |   7000 |       1 |        1 |              1 |
|  5 | Eve   |   6000 |       2 |        2 |              2 |
|  1 | Alice |   5000 |       3 |        3 |              3 |
|  2 | Bob   |   5000 |       4 |        3 |              3 |
|  3 | Cara  |   4000 |       5 |        5 |              4 |
+----+-------+--------+---------+----------+----------------+

For related functions, see MariaDB RANK(), DENSE_RANK(), and NTILE() examples.

For a cross-database comparison of top-N-per-group patterns and tie handling, see Top N Rows per Group by Database.