SQL DENSE_RANK

Summary: in this tutorial, you will learn how to use the SQL DENSE_RANK() function to rank rows in partitions with no gaps in ranking values.

An Overview of the SQL DENSE_RANK() function

The DENSE_RANK() is a window function that assigns ranks to rows in partitions with no gaps in the ranking values.

If two or more rows in each partition have the same values, they receive the same rank. The next row has the rank increased by one.

Different from the RANK() function, the DENSE_RANK() function always generates consecutive rank values.

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

DENSE_RANK() OVER (
	PARTITION BY expr1[{,expr2...}]
	ORDER BY expr1 [ASC|DESC], [{,expr2...}]
)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, the PARTITION BY clause divides the result set produced by the FROM clause into partitions.
• Then, The ORDER BY specifies the order of rows in each partition.
• Finally, the DENSE_RANK() function is applied to the rows in the specified order of each partition. It resets the rank when the partition boundary is crossed.

The following statements create a table named t and insert some rows into the table:

CREATE TABLE t (
	col CHAR
);
	
INSERT INTO t(col)
VALUES('A'),('B'),('B'),('C'),('D'),('D'),('E');
	
	
SELECT 
	*
FROM
	t;
Code language: SQL (Structured Query Language) (sql)

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

SELECT
	col,
	DENSE_RANK() OVER (
		ORDER BY col
	) my_dense_rank,
	RANK() OVER (
		ORDER BY col
	) my_rank
FROM
	t;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

SQL DENSE_RANK() function examples

We will use the employees and departments tables from the sample database for the demonstration purposes.

Using SQL DENSE_RANK() over the result set example

The following statement uses the DENSE_RANK() function to rank employees by their salaries:

SELECT 
	employee_id,
	first_name, 
	last_name, 
	salary, 
	DENSE_RANK() OVER (
		ORDER BY salary DESC
	) salary_rank
FROM 
	employees;
Code language: SQL (Structured Query Language) (sql)

In this example, we omitted the PARTITION BY clause, therefore, the DENSE_RANK() function treated the whole result set as a single partition.

First, the ORDER BY clause sorted the salaries in descending order. Then, the DENSE_RANK() function assigned a rank to each employee by his/her salary amount.

The following picture shows the partial output of the query:

Using SQL DENSE_RANK() over partition example

The following statement ranks employees in each department by their salaries:

SELECT 
	first_name, 
	last_name, 
	department_name,
	salary, 
	DENSE_RANK() OVER (
		PARTITION BY department_name
		ORDER BY salary DESC) salary_rank
FROM 
	employees e
	INNER JOIN departments d 
		ON d.department_id = e.department_id;
Code language: SQL (Structured Query Language) (sql)

In this example:

• First, the PARTITION BY clause divided the employees by department names into partitions.
• Then, the ORDER BY clause sorted the employees in each department (partition) by their salaries.
• Finally, the DENSE_RANK() function was applied to each partition to assign the rank to rows based on the salary order.

The following picture shows the partial output of the query:

If you want to find only employees who have the highest salary in their departments, you just to use a subquery in the FROM clause as follows:

SELECT 
	* 
FROM (
	SELECT 
		first_name, 
		last_name, 
		department_name,
		salary, 
		DENSE_RANK() OVER (
			PARTITION BY department_name
			ORDER BY salary DESC) salary_rank
	FROM 
		employees e
		INNER JOIN departments d 
			ON d.department_id = e.department_id
	) t
WHERE 
	salary_rank = 1;Code language: SQL (Structured Query Language) (sql)

The following output shows the employees who have the highest salary in their department:

In this tutorial, you have learned how to use the SQL DENSE_RANK() function to rank rows in partitions with no gap in ranking values.