SQL Server DENSE_RANK Function

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

Summary: in this tutorial, you will learn how to use the SQL Server DENSE_RANK() function to assign a rank to each row within a partition of a result set, with no gaps in ranking values.

Introduction to SQL Server DENSE_RANK() function #

The DENSE_RANK() is a window function that assigns a rank to each row within a partition of a result set. Unlike the RANK() function, the DENSE_RANK() function returns consecutive rank values. Rows in each partition receive the same ranks if they have the same values.

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

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
Copy

The DENSE_RANK() function is applied to the rows of each partition defined by the PARTITION BY clause, in a specified order, defined by ORDER BY clause. It resets the rank when the partition boundary is crossed.

The PARITION BY clause is optional. If you omit it, the function will treat the whole result set as a single partition.

SQL Server DENSE_RANK() function illustration #

The following statements create a new table named dense_rank_demo and insert some rows into that table:

CREATE TABLE sales.dense_rank_demo (
	v VARCHAR(10)
);
	
INSERT INTO sales.dense_rank_demo(v)
VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');
	
SELECT * FROM sales.dense_rank_demo;
Copy

The following statement uses both DENSE_RANK() and RANK() functions to assign a rank to each row of the result set:

SELECT
	v,
	DENSE_RANK() OVER (
		ORDER BY v
	) my_dense_rank,
	RANK() OVER (
		ORDER BY v
	) my_rank
FROM
	sales.dense_rank_demo;
Copy

Here is the output:

SQL Server DENSE_RANK() function examples #

We will use the production.products table to demonstrate the DENSE_RANK() function:

Using SQL Server DENSE_RANK() over a result set example #

The following example uses the DENSE_RANK() function to rank products by list prices:

SELECT
	product_id,
	product_name,
	list_price,
	DENSE_RANK () OVER ( 
		ORDER BY list_price DESC
	) price_rank 
FROM
	production.products;
Copy

Here is the output:

Using SQL Server DENSE_RANK() over partitions example #

The following statement ranks products in each category by list prices. It returns only the top 3 products per category by list prices.

SELECT * FROM (
	SELECT
		product_id,
		product_name,
		category_id,
		list_price,
		DENSE_RANK () OVER ( 
			PARTITION BY category_id
			ORDER BY list_price DESC
		) price_rank 
	FROM
		production.products
) t
WHERE price_rank < 3;
Copy

The following picture shows the output:

In this tutorial, you have learned how to use the SQL Server DENSE_RANK() function to assign a rank to each row within a partition of a result set, with no gaps in rank values.