How ROW_NUMBER() Works in SQL Server

In SQL Server, the ROW_NUMBER() function allows you to number the output of a result set. It returns the sequential number of each row, starting at 1.

If you specify partitions for the result set, each partition causes the numbering to start over again (i.e. the numbering will start at 1 for the first row in each partition).

Syntax

The syntax goes like this:

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

PARTITION BY value_expression is optional. It divides the result set produced by the FROM clause into partitions to which the function is applied. value_expression specifies the column by which the result set is partitioned. If the PARTITION BY clause is not specified, all rows of the query result set are treated as a single group.

<order_by_clause> is required. It determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

Note that the OVER clause normally accepts a <ROW or RANGE clause>, but that argument cannot be used with this function.

Example 1 – Basic Usage

Here’s a basic example showing how this function works:

SELECT
  ROW_NUMBER() OVER (ORDER BY AlbumId ASC) 'Row',
  AlbumId,
  AlbumName
FROM Albums;

Result:

+-------+-----------+--------------------------+
| Row   | AlbumId   | AlbumName                |
|-------+-----------+--------------------------|
| 1     | 1         | Powerslave               |
| 2     | 2         | Powerage                 |
| 3     | 3         | Singing Down the Lane    |
| 4     | 4         | Ziltoid the Omniscient   |
| 5     | 5         | Casualties of Cool       |
| 6     | 6         | Epicloud                 |
| 7     | 7         | Somewhere in Time        |
| 8     | 8         | Piece of Mind            |
| 9     | 9         | Killers                  |
| 10    | 10        | No Prayer for the Dying  |
| 11    | 11        | No Sound Without Silence |
| 12    | 12        | Big Swing Face           |
| 13    | 13        | Blue Night               |
| 14    | 14        | Eternity                 |
| 15    | 15        | Scandinavia              |
| 16    | 16        | Long Lost Suitcase       |
| 17    | 17        | Praise and Blame         |
| 18    | 18        | Along Came Jones         |
| 19    | 19        | All Night Wrong          |
| 20    | 20        | The Sixteen Men of Tain  |
| 21    | 21        | Yo Wassup                |
| 22    | 22        | Busted                   |
+-------+-----------+--------------------------+

In this case we can see that the row numbers align perfectly with values in the AlbumId column. This is purely coincidental. This happens because the AlbumId column is using an incrementing value starting at 1, which is also what ROW_NUMBER() uses.

The row numbering is correlated to the AlbumId column to the extent that it’s ordered by that column. But it doesn’t mean that the values need to be the same.

Example 2 – Add a WHERE Clause

Adding a WHERE clause will demonstrate what I mean.

SELECT
  ROW_NUMBER() OVER (ORDER BY AlbumId ASC) 'Row',
  AlbumId,
  AlbumName
FROM Albums
WHERE AlbumId > 15;

Result:

+-------+-----------+-------------------------+
| Row   | AlbumId   | AlbumName               |
|-------+-----------+-------------------------|
| 1     | 16        | Long Lost Suitcase      |
| 2     | 17        | Praise and Blame        |
| 3     | 18        | Along Came Jones        |
| 4     | 19        | All Night Wrong         |
| 5     | 20        | The Sixteen Men of Tain |
| 6     | 21        | Yo Wassup               |
| 7     | 22        | Busted                  |
+-------+-----------+-------------------------+

Example 3 – Switch the Ordering

Ordering by descending order instead of ascending also demonstrates this concept.

SELECT
  ROW_NUMBER() OVER (ORDER BY AlbumId DESC) 'Row',
  AlbumId,
  AlbumName
FROM Albums;

Result:

+-------+-----------+--------------------------+
| Row   | AlbumId   | AlbumName                |
|-------+-----------+--------------------------|
| 1     | 22        | Busted                   |
| 2     | 21        | Yo Wassup                |
| 3     | 20        | The Sixteen Men of Tain  |
| 4     | 19        | All Night Wrong          |
| 5     | 18        | Along Came Jones         |
| 6     | 17        | Praise and Blame         |
| 7     | 16        | Long Lost Suitcase       |
| 8     | 15        | Scandinavia              |
| 9     | 14        | Eternity                 |
| 10    | 13        | Blue Night               |
| 11    | 12        | Big Swing Face           |
| 12    | 11        | No Sound Without Silence |
| 13    | 10        | No Prayer for the Dying  |
| 14    | 9         | Killers                  |
| 15    | 8         | Piece of Mind            |
| 16    | 7         | Somewhere in Time        |
| 17    | 6         | Epicloud                 |
| 18    | 5         | Casualties of Cool       |
| 19    | 4         | Ziltoid the Omniscient   |
| 20    | 3         | Singing Down the Lane    |
| 21    | 2         | Powerage                 |
| 22    | 1         | Powerslave               |
+-------+-----------+--------------------------+

Example 4 – Order by a Different Column

And while we’re at it, let’s order by the AlbumName column instead.

SELECT
  ROW_NUMBER() OVER (ORDER BY AlbumName ASC) 'Row',
  AlbumId,
  AlbumName
FROM Albums;

Result:

+-------+-----------+--------------------------+
| Row   | AlbumId   | AlbumName                |
|-------+-----------+--------------------------|
| 1     | 19        | All Night Wrong          |
| 2     | 18        | Along Came Jones         |
| 3     | 12        | Big Swing Face           |
| 4     | 13        | Blue Night               |
| 5     | 22        | Busted                   |
| 6     | 5         | Casualties of Cool       |
| 7     | 6         | Epicloud                 |
| 8     | 14        | Eternity                 |
| 9     | 9         | Killers                  |
| 10    | 16        | Long Lost Suitcase       |
| 11    | 10        | No Prayer for the Dying  |
| 12    | 11        | No Sound Without Silence |
| 13    | 8         | Piece of Mind            |
| 14    | 2         | Powerage                 |
| 15    | 1         | Powerslave               |
| 16    | 17        | Praise and Blame         |
| 17    | 15        | Scandinavia              |
| 18    | 3         | Singing Down the Lane    |
| 19    | 7         | Somewhere in Time        |
| 20    | 20        | The Sixteen Men of Tain  |
| 21    | 21        | Yo Wassup                |
| 22    | 4         | Ziltoid the Omniscient   |
+-------+-----------+--------------------------+

Example 5 – Partitions

As mentioned, you can also divide the results into partitions. When you do this, the numbering starts at 1 again for each new partition.

Example:

SELECT
  Genre,
  ROW_NUMBER() OVER (PARTITION BY Genre ORDER BY AlbumId ASC) 'Row',
  AlbumId,
  AlbumName
FROM Albums
INNER JOIN Genres 
ON Albums.GenreId = Genres.GenreId;

Result:

+---------+-------+-----------+--------------------------+
| Genre   | Row   | AlbumId   | AlbumName                |
|---------+-------+-----------+--------------------------|
| Country | 1     | 3         | Singing Down the Lane    |
| Country | 2     | 21        | Yo Wassup                |
| Country | 3     | 22        | Busted                   |
| Jazz    | 1     | 12        | Big Swing Face           |
| Jazz    | 2     | 19        | All Night Wrong          |
| Jazz    | 3     | 20        | The Sixteen Men of Tain  |
| Pop     | 1     | 11        | No Sound Without Silence |
| Pop     | 2     | 13        | Blue Night               |
| Pop     | 3     | 14        | Eternity                 |
| Pop     | 4     | 15        | Scandinavia              |
| Pop     | 5     | 16        | Long Lost Suitcase       |
| Pop     | 6     | 17        | Praise and Blame         |
| Pop     | 7     | 18        | Along Came Jones         |
| Rock    | 1     | 1         | Powerslave               |
| Rock    | 2     | 2         | Powerage                 |
| Rock    | 3     | 4         | Ziltoid the Omniscient   |
| Rock    | 4     | 5         | Casualties of Cool       |
| Rock    | 5     | 6         | Epicloud                 |
| Rock    | 6     | 7         | Somewhere in Time        |
| Rock    | 7     | 8         | Piece of Mind            |
| Rock    | 8     | 9         | Killers                  |
| Rock    | 9     | 10        | No Prayer for the Dying  |
+---------+-------+-----------+--------------------------+

Once again we can see that the ROW_NUMBER and the AlbumId columns are completely uncorrelated.

In this case I partition by the Genre column. This causes the numbering to start at 1 again for each genre.