The SQL Server DENSE_RANK function is a ranking function that assigns a rank to each row in a result set, starting with 1 for the first row. Unlike the RANK function, DENSE_RANK does not skip ranks when there are ties. This can be useful when you want to know the relative position of rows within a result set.

Syntax

DENSE_RANK() OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)

Like RANK, DENSE RANK will assign the same rank to tied rows. DENSE RANK, on the other hand, will not omit any digits. As a result, in the case of a tie for first place between the top two rows, each will receive a rank of 1. In contrast to RANK, which would have given the third row a ranking of 3, this system assigns a rank of 2.

DENSE_RANK with OVER clause

Here is an example of how the DENSE_RANK function with OVER clause can be used to dense_rank rows in a query:

SELECT
DENSE_RANK() OVER(ORDER BY price, name) AS DR,
price, name
FROM ebooks;

DENSE_RANK with PARTITION BY clause

The following example shows how the DENSE_RANK function can be used with the PARTITION BY clause:

SELECT name, price,
DENSE_RANK() OVER (PARTITION BY price ORDER BY name) AS DR
FROM ebooks;

DENSE_RANK with TOP

For example, the following query uses the DENSE_RANK function to return the top 5 ebooks by price:

SELECT TOP 5
DENSE_RANK() OVER(ORDER BY price DESC) AS DR,
price, name
FROM ebooks;