SQL Server DENSE_RANK() Function

In SQL Server, the DENSE_RANK() function is a ranking function that assigns a rank value to each row in a query result set. Similar to the RANK() function, the DENSE_RANK() function sorts the query result set according to a specified sorting order and assigns a rank value to each row. The difference is that the DENSE_RANK() function skips duplicate rank values and does not leave gaps, even if there are rows with the same rank value. This allows the DENSE_RANK() function to generate a ranking list with continuous rank values.

Syntax

The syntax of the DENSE_RANK() function is as follows:

DENSE_RANK() OVER (
  [ PARTITION BY partition_expression , ... [ n ] ]
  ORDER BY order_expression [ ASC | DESC ] , ... [ n ] )

The PARTITION BY clause specifies grouping conditions and the ORDER BY clause specifies sorting conditions.

Use Cases

The DENSE_RANK() function is typically used in the following scenarios:

Ranking a data set and retaining duplicate values while not leaving gaps
Finding the rank of a specific row in a given data set
Finding the relative rank of a specific row in a data set

Examples

Assuming the following sales table:

id	name	amount
1	John	1000
2	Alice	2000
3	Tom	1500
4	Jack	2000
5	Lucy	1500
6	Bob	1000

Example 1

Query the rank of each person in the sales table, and sort by sales amount in descending order:

SELECT name, amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales;

The query result is as follows:

name	amount	rank
Alice	2000	1
Jack	2000	1
Tom	1500	2
Lucy	1500	2
John	1000	3
Bob	1000	3

Example 2

Query the rank of each person in the sales table, and sort by name in ascending order:

SELECT name, amount, DENSE_RANK() OVER (ORDER BY name ASC) AS rank
FROM sales;