In SQL Server, the RANK() function returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row.
Syntax
The syntax goes like this:
RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause is optional. It divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
order_by_clause is required. It determines the order of the data before the function is applied.
Note that the OVER clause normally accepts a rows_or_range_clause, but that argument cannot be used with the RANK() function.
Example 1 – Basic Usage
Here’s a basic example showing the usage of the RANK() function:
SELECT AlbumId, AlbumName, ArtistId, RANK() OVER (ORDER BY ArtistId ASC) 'Rank' FROM Albums;
Result:
+-----------+--------------------------+------------+--------+ | AlbumId | AlbumName | ArtistId | Rank | |-----------+--------------------------+------------+--------| | 1 | Powerslave | 1 | 1 | | 7 | Somewhere in Time | 1 | 1 | | 8 | Piece of Mind | 1 | 1 | | 9 | Killers | 1 | 1 | | 10 | No Prayer for the Dying | 1 | 1 | | 2 | Powerage | 2 | 6 | | 19 | All Night Wrong | 3 | 7 | | 20 | The Sixteen Men of Tain | 3 | 7 | | 12 | Big Swing Face | 4 | 9 | | 4 | Ziltoid the Omniscient | 5 | 10 | | 5 | Casualties of Cool | 5 | 10 | | 6 | Epicloud | 5 | 10 | | 3 | Singing Down the Lane | 6 | 13 | | 16 | Long Lost Suitcase | 7 | 14 | | 17 | Praise and Blame | 7 | 14 | | 18 | Along Came Jones | 7 | 14 | | 11 | No Sound Without Silence | 9 | 17 | | 21 | Yo Wassup | 9 | 17 | | 22 | Busted | 9 | 17 | | 13 | Blue Night | 12 | 20 | | 14 | Eternity | 12 | 20 | | 15 | Scandinavia | 12 | 20 | +-----------+--------------------------+------------+--------+
Our main focus is the ArtistId and Rank columns. We can see that the rank increases each time the ArtistId increases. This is because I’m ordering by ArtistId, and so each new artist will get a new rank.
When we look at the Rank column, we can see quite a few ties. That is, quite a few rows share the same rank. This is to be expected, because I’m ordering by ArtistId and some ArtistId values are in more than one row.
These tied rows are great for demonstrating how RANK() works. As mentioned, it increments by one plus the number of ranks that came before it. Tied rows cause gaps to appear in the ranking values (i.e. they don’t always increment by 1). In the above example, there are quite a few gaps. The first one is where it goes from 1 to 6. Then another one when it goes from 7 to 9, and so on.
If you don’t want these gaps, use DENSE_RANK(), which works the same way except without gaps. The dense rank is calculated as one plus the number of distinct rank values that come before that row.
Example 2 – Partitions
You can also divide the results into partitions. When you do this, the rank is calculated against each partition (so it starts over again with each new partition).
Example:
SELECT Genre, AlbumName, ArtistId, RANK() OVER (PARTITION BY Genre ORDER BY ArtistId ASC) 'Rank' FROM Albums INNER JOIN Genres ON Albums.GenreId = Genres.GenreId;
Result:
+---------+--------------------------+------------+--------+ | Genre | AlbumName | ArtistId | Rank | |---------+--------------------------+------------+--------| | Country | Singing Down the Lane | 6 | 1 | | Country | Yo Wassup | 9 | 2 | | Country | Busted | 9 | 2 | | Jazz | All Night Wrong | 3 | 1 | | Jazz | The Sixteen Men of Tain | 3 | 1 | | Jazz | Big Swing Face | 4 | 3 | | Pop | Long Lost Suitcase | 7 | 1 | | Pop | Praise and Blame | 7 | 1 | | Pop | Along Came Jones | 7 | 1 | | Pop | No Sound Without Silence | 9 | 4 | | Pop | Blue Night | 12 | 5 | | Pop | Eternity | 12 | 5 | | Pop | Scandinavia | 12 | 5 | | Rock | Powerslave | 1 | 1 | | Rock | Somewhere in Time | 1 | 1 | | Rock | Piece of Mind | 1 | 1 | | Rock | Killers | 1 | 1 | | Rock | No Prayer for the Dying | 1 | 1 | | Rock | Powerage | 2 | 6 | | Rock | Ziltoid the Omniscient | 5 | 7 | | Rock | Casualties of Cool | 5 | 7 | | Rock | Epicloud | 5 | 7 | +---------+--------------------------+------------+--------+
In this case I partition by Genre. This causes each row to only be ranked against the other rows in the same partition. So each partition causes the ranking value to start at 1 again.
Example 3 – A Scoreboard Example
Here’s a possible use case for displaying the rank to the user.
SELECT Player, Score, RANK() OVER (ORDER BY Score Desc) 'Rank' FROM Scoreboard;
Result:
+----------+---------+--------+ | Player | Score | Rank | |----------+---------+--------| | Bart | 2010 | 1 | | Burns | 1270 | 2 | | Meg | 1030 | 3 | | Marge | 990 | 4 | | Lisa | 710 | 5 | | Ned | 666 | 6 | | Apu | 350 | 7 | | Homer | 1 | 8 | +----------+---------+--------+
However, be mindful that any tied results will result in gaps in the ranking values.
Here’s what happens if Lisa suddenly matches Bart’s score:
SELECT Player, Score, RANK() OVER (ORDER BY Score Desc) 'Rank' FROM Scoreboard;
Result:
+----------+---------+--------+ | Player | Score | Rank | |----------+---------+--------| | Lisa | 2010 | 1 | | Bart | 2010 | 1 | | Burns | 1270 | 3 | | Meg | 1030 | 4 | | Marge | 990 | 5 | | Ned | 666 | 6 | | Apu | 350 | 7 | | Homer | 1 | 8 | +----------+---------+--------+
In this case nobody is ranked at number 2, because the first two players are tied at rank 1.
As mentioned, if you need to eliminate gaps such as this one, use DENSE_RANK().
Example 4 – Replacing RANK() with DENSE_RANK()
Here’s the same example again, except this time I use DENSE_RANK():
SELECT Player, Score, DENSE_RANK() OVER (ORDER BY Score Desc) 'Rank' FROM Scoreboard;
Result:
+----------+---------+--------+ | Player | Score | Rank | |----------+---------+--------| | Lisa | 2010 | 1 | | Bart | 2010 | 1 | | Burns | 1270 | 2 | | Meg | 1030 | 3 | | Marge | 990 | 4 | | Ned | 666 | 5 | | Apu | 350 | 6 | | Homer | 1 | 7 | +----------+---------+--------+