In MySQL, ROW_NUMBER() is a window function that returns the number of the current row within its partition. Numbering starts at 1 and increments sequentially.

Syntax

The syntax goes like this:

ROW_NUMBER() over_clause

The OVER clause determines how the rowset is partitioned and ordered before the window function is applied.

Example

Here’s a basic example to demonstrate how it works:

SELECT 
    DogName,
    Activity,
    Score,
    ROW_NUMBER() OVER ( ORDER BY Score ) AS "Row Number"
FROM Dogs;

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Row Number |
+---------+-------------+-------+------------+
| Max     | Keep Quiet  |     1 |          1 |
| Bruno   | Keep Quiet  |     2 |          2 |
| Cooper  | Keep Quiet  |     8 |          3 |
| Bruno   | Fetch Stick |    43 |          4 |
| Cooper  | Wag Tail    |    51 |          5 |
| Bruno   | Wag Tail    |    65 |          6 |
| Cooper  | Fetch Stick |    67 |          7 |
| Max     | Wag Tail    |    87 |          8 |
| Max     | Fetch Stick |    91 |          9 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

We haven’t applied any partitioning here, and so the numbering increments throughout the whole rowset.

If we omit the ORDER BY clause, the ordering is nondeterministic:

SELECT 
    DogName,
    Activity,
    Score,
    ROW_NUMBER() OVER () AS "Row Number"
FROM Dogs;

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Row Number |
+---------+-------------+-------+------------+
| Bruno   | Fetch Stick |    43 |          1 |
| Cooper  | Fetch Stick |    67 |          2 |
| Max     | Fetch Stick |    91 |          3 |
| Bruno   | Wag Tail    |    65 |          4 |
| Cooper  | Wag Tail    |    51 |          5 |
| Max     | Wag Tail    |    87 |          6 |
| Bruno   | Keep Quiet  |     2 |          7 |
| Cooper  | Keep Quiet  |     8 |          8 |
| Max     | Keep Quiet  |     1 |          9 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

Either way, the ROW_NUMBER() function continues to return the row number.

Partitioning

This example uses a PARTITION BY clause to partition the results by activity:

SELECT 
    DogName,
    Activity,
    Score,
    ROW_NUMBER( )
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score
            )
    AS "Row Number"
FROM Dogs;

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Row Number |
+---------+-------------+-------+------------+
| Bruno   | Fetch Stick |    43 |          1 |
| Cooper  | Fetch Stick |    67 |          2 |
| Max     | Fetch Stick |    91 |          3 |
| Max     | Keep Quiet  |     1 |          1 |
| Bruno   | Keep Quiet  |     2 |          2 |
| Cooper  | Keep Quiet  |     8 |          3 |
| Cooper  | Wag Tail    |    51 |          1 |
| Bruno   | Wag Tail    |    65 |          2 |
| Max     | Wag Tail    |    87 |          3 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

Here, each partition contains three rows, and the numbering resets with each partition.

Changing the Order

Here’s what happens when we change the order to descending order:

SELECT 
    DogName,
    Activity,
    Score,
    ROW_NUMBER( )
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score DESC
            )
    AS "Row Number"
FROM Dogs;

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Row Number |
+---------+-------------+-------+------------+
| Max     | Fetch Stick |    91 |          1 |
| Cooper  | Fetch Stick |    67 |          2 |
| Bruno   | Fetch Stick |    43 |          3 |
| Cooper  | Keep Quiet  |     8 |          1 |
| Bruno   | Keep Quiet  |     2 |          2 |
| Max     | Keep Quiet  |     1 |          3 |
| Max     | Wag Tail    |    87 |          1 |
| Bruno   | Wag Tail    |    65 |          2 |
| Cooper  | Wag Tail    |    51 |          3 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

So the data is returned in a different order but the numbering remains the same (i.e. 1, 2, 3, etc).

Here’ it is again, but with ROW_NUMBER() applied to two different columns, each with different ordering:

SELECT 
    DogName,
    Activity,
    Score,
    ROW_NUMBER( )
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score ASC
            )
    AS "Row Number 1",
    ROW_NUMBER( )
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score DESC
            )
    AS "Row Number 2"
FROM Dogs;

Result:

+---------+-------------+-------+--------------+--------------+
| DogName | Activity    | Score | Row Number 1 | Row Number 2 |
+---------+-------------+-------+--------------+--------------+
| Max     | Fetch Stick |    91 |            3 |            1 |
| Cooper  | Fetch Stick |    67 |            2 |            2 |
| Bruno   | Fetch Stick |    43 |            1 |            3 |
| Cooper  | Keep Quiet  |     8 |            3 |            1 |
| Bruno   | Keep Quiet  |     2 |            2 |            2 |
| Max     | Keep Quiet  |     1 |            1 |            3 |
| Max     | Wag Tail    |    87 |            3 |            1 |
| Bruno   | Wag Tail    |    65 |            2 |            2 |
| Cooper  | Wag Tail    |    51 |            1 |            3 |
+---------+-------------+-------+--------------+--------------+
9 rows in set (0.00 sec)

Here, the Row Number 1 column is numbered in descending order, while the other one is in ascending order.

Peers/Ties

MySQL provides some ranking functions, such as RANK() and DENSE_RANK() that are similar to ROW_NUMBER(). But a key difference is that those ranking functions allow ties, whereas ROW_NUMBER() doesn’t.

What I mean is that, if two or more rows tie for the same rank, RANK() and DENSE_RANK() will assign the same numeric value to those rows. The ROW_NUMBER() function on the other hand will not do this. It will continue to increment its sequential value as it would any other row.

Therefore, if you want peers/ties to share the same number, use RANK() or DENSE_RANK() instead. Note that these two functions work slightly differently to each other (RANK() will produce gaps whenever there’s a tie, whereas DENSE_RANK() won’t) , so be sure to choose the right function for your needs.