In SQL Server, the DENSE_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 distinct ranks that come before the row.
This function is similar to RANK(), but without the gaps in the ranking values that can occur with RANK() when ties exist in the result set.
Syntax
The syntax goes like this:
DENSE_RANK ( ) OVER ( [] < 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, all rows of the query result set are treated as a single group.
<order_by_clause> is required. It determines the order in which the function applies to the rows in a partition.
Note that the OVER clause normally accepts a <rows_or_range_clause>, but that argument cannot be used with this function.
Example 1 – Basic Usage
Here’s a basic example showing the usage of the DENSE_RANK() function:
SELECT AlbumId, AlbumName, ArtistId, DENSE_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 | 2 | | 19 | All Night Wrong | 3 | 3 | | 20 | The Sixteen Men of Tain | 3 | 3 | | 12 | Big Swing Face | 4 | 4 | | 4 | Ziltoid the Omniscient | 5 | 5 | | 5 | Casualties of Cool | 5 | 5 | | 6 | Epicloud | 5 | 5 | | 3 | Singing Down the Lane | 6 | 6 | | 16 | Long Lost Suitcase | 7 | 7 | | 17 | Praise and Blame | 7 | 7 | | 18 | Along Came Jones | 7 | 7 | | 11 | No Sound Without Silence | 9 | 8 | | 21 | Yo Wassup | 9 | 8 | | 22 | Busted | 9 | 8 | | 13 | Blue Night | 12 | 9 | | 14 | Eternity | 12 | 9 | | 15 | Scandinavia | 12 | 9 | +-----------+--------------------------+------------+--------+
Look at the ArtistId and Rank columns. 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.
The rank remains the same with each artist, regardless of how many rows contain the same ArtistId, because the results are ordered by that column. For example, five rows contain the same ArtistId and therefore they also contain the same rank. In other words, they’re all tied for rank 1.
In many rows, the rank happens to be identical to the ArtistId, but this is just a coincidence. It just so happens that the ArtistId is an IDENTITY column that starts at 1 and increments by 1, which is also what RANK() does. However, you’ll see that they’re not identical on all rows. For example, the ArtistId skips from 7 to 9, but the rank simply increments from 7 to 8, and from that point on, both columns contain different values.
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, DENSE_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 | 2 | | Pop | Long Lost Suitcase | 7 | 1 | | Pop | Praise and Blame | 7 | 1 | | Pop | Along Came Jones | 7 | 1 | | Pop | No Sound Without Silence | 9 | 2 | | Pop | Blue Night | 12 | 3 | | Pop | Eternity | 12 | 3 | | Pop | Scandinavia | 12 | 3 | | 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 | 2 | | Rock | Ziltoid the Omniscient | 5 | 3 | | Rock | Casualties of Cool | 5 | 3 | | Rock | Epicloud | 5 | 3 | +---------+--------------------------+------------+--------+
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, DENSE_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 | +----------+---------+--------+
Be mindful that any tied results won’t affect subsequent ranks. In other words, there will be no gaps in the rank value.
This is probably best explained with an example:
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 | +----------+---------+--------+
In this case Lisa and Bart are tied at number 1. Then Burns comes in at number 2 (even though he’s the third person).
If you prefer that Burns was ranked number 3 in this case (and Meg at 4, and so on), use the RANK() function instead.
Example 4 – Replacing DENSE_RANK() with RANK()
Here’s the same example again, except this time I use RANK():
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 | +----------+---------+--------+