Many relational database management systems (RDBMSs) provide a DENSE_RANK() function that we can use in our SQL queries. The SQL DENSE_RANK() function is a window function that returns the rank of the current row within its partition, without gaps.
The “without gaps” part is what distinguishes the DENSE_RANK() function from the RANK() function.
DENSE_RANK() returns contiguous rank numbers whenever there are ties, whereas RANK() will leave a gap between the tie and the next rank, resulting in noncontiguous rank numbers.
Example
Here’s an example of a SQL query that includes the DENSE_RANK() function:
SELECT
IdiotName,
IQ,
DENSE_RANK() OVER ( ORDER BY IQ ) AS "Dense Rank"
FROM Idiots;Result:
+-------------------+------+------------+ | IdiotName | IQ | Dense Rank | +-------------------+------+------------+ | Dumbest | 30 | 1 | | Homer | 40 | 2 | | Patrick Star | 40 | 2 | | Ed | 40 | 2 | | Dumber | 50 | 3 | | Peter Griffin | 50 | 3 | | Cosmo | 55 | 4 | | Dumb | 60 | 5 | | Ralph Wiggum | 65 | 6 | | Richard Watterson | 70 | 7 | +-------------------+------+------------+ 10 rows in set (0.00 sec)
This table has an IQ column that contains numeric values. And we use the DENSE_RANK() function to rank the values in the IQ column.
We use an OVER clause to determine how the rows are ordered before ranking the values. We could also use this clause to partition the rows (more on this later).
In our example we have two instances of a tie. We can see that three idiots have a rank of 2, and two idiots have a rank of 3. The reason some idiots share the same rank is because they have the same IQ.
It’s important to note that there are no gaps after the tied rows. For example, even though three rows share a rank of 2, the next rank value is 3. It doesn’t skip forward by three rank values to 5 (which is what would happen if we used the RANK() function).
Here’s an example that demonstrates the difference between these two functions:
SELECT
IdiotName,
IQ,
DENSE_RANK() OVER win AS "Dense Rank",
RANK() OVER win AS "Rank"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+------------+------+ | IdiotName | IQ | Dense Rank | Rank | +-------------------+------+------------+------+ | Dumbest | 30 | 1 | 1 | | Homer | 40 | 2 | 2 | | Patrick Star | 40 | 2 | 2 | | Ed | 40 | 2 | 2 | | Dumber | 50 | 3 | 5 | | Peter Griffin | 50 | 3 | 5 | | Cosmo | 55 | 4 | 7 | | Dumb | 60 | 5 | 8 | | Ralph Wiggum | 65 | 6 | 9 | | Richard Watterson | 70 | 7 | 10 | +-------------------+------+------------+------+ 10 rows in set (0.01 sec)
See how the Rank column skips forward whenever there’s a tie? In other words, the ranking values end up with gaps whenever there’s a tie. If there weren’t any ties, then there would be no gaps and both functions would return the same result.
I also made another change in this example. I moved the ORDER BY clause to a named window. This allowed me to use the same ORDER BY clause twice (saving me from having to type it out twice). Instead, all we need to do is provide the name of the named window (in this case, win).
Your ability to use a named window may depend on your DBMS. For example, at the time of writing, SQL Server has only just introduced this capability in SQL Server 2022. Trying to use the WINDOW clause in earlier versions will result in an error.
Reorder the Rankings
In the above examples, the idiot with the lowest IQ gets the highest rank (assuming a rank of 1 is higher than 2, etc).
Let’s switch it around so that the highest IQ gets the highest rank:
SELECT
IdiotName,
IQ,
DENSE_RANK() OVER win AS "Dense Rank",
RANK() OVER win AS "Rank"
FROM Idiots
WINDOW win AS ( ORDER BY IQ DESC );Result:
+-------------------+------+------------+------+ | IdiotName | IQ | Dense Rank | Rank | +-------------------+------+------------+------+ | Richard Watterson | 70 | 1 | 1 | | Ralph Wiggum | 65 | 2 | 2 | | Dumb | 60 | 3 | 3 | | Cosmo | 55 | 4 | 4 | | Dumber | 50 | 5 | 5 | | Peter Griffin | 50 | 5 | 5 | | Homer | 40 | 6 | 7 | | Patrick Star | 40 | 6 | 7 | | Ed | 40 | 6 | 7 | | Dumbest | 30 | 7 | 10 | +-------------------+------+------------+------+ 10 rows in set (0.00 sec)
Omitting the ORDER BY Clause
We should use the DENSE_RANK() function with an ORDER BY clause if we want meaningful results. Omitting the ORDER BY clause will cause all rows to be peers (and therefore they’ll all share the same rank).
Here’s what happens when we omit the ORDER BY clause in MySQL:
SELECT
IdiotName,
IQ,
DENSE_RANK() OVER () AS "Dense Rank"
FROM Idiots;Result:
+-------------------+------+------------+ | IdiotName | IQ | Dense Rank | +-------------------+------+------------+ | Dumb | 60 | 1 | | Dumber | 50 | 1 | | Dumbest | 30 | 1 | | Homer | 40 | 1 | | Peter Griffin | 50 | 1 | | Patrick Star | 40 | 1 | | Ed | 40 | 1 | | Ralph Wiggum | 65 | 1 | | Cosmo | 55 | 1 | | Richard Watterson | 70 | 1 | +-------------------+------+------------+ 10 rows in set (0.00 sec)
All rows contain a dense rank of 1.
Some DBMSs (such as SQL Server) return an error if we omit the ORDER BY clause.
Here’s the error we get when we omit the ORDER BY clause in SQL Server:
Msg 4112, Level 15, State 1, Line 5 The function 'DENSE_RANK' must have an OVER clause with ORDER BY.
Partitions
We can use the PARTITION BY clause to divide the result set produced by the FROM clause into partitions, so that the DENSE_RANK() function is applied against each partition. This results in each value only being evaluated against the other rows in the same partition.
Here’s an example that demonstrates this:
SELECT
DogName,
Activity,
Score,
DENSE_RANK() OVER ( PARTITION BY Activity ORDER BY Score ) AS "Dense Rank"
FROM Dogs;Result:
+---------+-------------+-------+------------+ | DogName | Activity | Score | Dense Rank | +---------+-------------+-------+------------+ | 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, I’ve partitioned the results by the Activity column. There are three activities, and so we get three partitions. The ranking values reset with each partition. Therefore, the rank starts at 1 again at the start of each partition.
Similar Window Functions in SQL
Here’s an example that compares the DENSE_RANK() function to other similar SQL functions:
SELECT
DogName,
Activity,
Score,
DENSE_RANK() OVER win AS "Dense Rank",
RANK() OVER win AS "Rank",
PERCENT_RANK() OVER win AS "Percentage Rank",
CUME_DIST() OVER win AS "Cumulative Distribution"
FROM Dogs
WINDOW win AS (PARTITION BY Activity ORDER BY Score);Result:
+---------+-------------+-------+------------+------+-----------------+-------------------------+ | DogName | Activity | Score | Dense Rank | Rank | Percentage Rank | Cumulative Distribution | +---------+-------------+-------+------------+------+-----------------+-------------------------+ | Bruno | Fetch Stick | 43 | 1 | 1 | 0 | 0.3333333333333333 | | Cooper | Fetch Stick | 67 | 2 | 2 | 0.5 | 0.6666666666666666 | | Max | Fetch Stick | 91 | 3 | 3 | 1 | 1 | | Bruno | Keep Quiet | 1 | 1 | 1 | 0 | 0.3333333333333333 | | Cooper | Keep Quiet | 8 | 2 | 2 | 0.5 | 0.6666666666666666 | | Max | Keep Quiet | 12 | 3 | 3 | 1 | 1 | | Bruno | Wag Tail | 51 | 1 | 1 | 0 | 0.6666666666666666 | | Cooper | Wag Tail | 51 | 1 | 1 | 0 | 0.6666666666666666 | | Max | Wag Tail | 87 | 2 | 3 | 1 | 1 | +---------+-------------+-------+------------+------+-----------------+-------------------------+ 9 rows in set (0.00 sec)
As with any SQL functionality, the availability and exact syntax of each function will depend on your DBMS. Best to check with your DBMS’s documentation for a complete overview of the syntax.