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.