DENSE_RANK Function - SQL

Overview

The DENSE_RANK function in SQL assigns ranks to rows within a partition of a result set, with no gaps in ranking values. It is useful for ranking rows with tied values without skipping subsequent ranks.

Example:

SELECT department_id, first_name, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM company.employees

Syntax:

SELECT column_name,
DENSE_RANK() OVER (PARTITION BY partition_column[s] ORDER BY order_column[s])
FROM table_name

partition_column[s] is the column or columns that divide the result set into partitions.

order_column[s] is the column or columns that specify the order of rows within each partition.

DENSE_RANK() assigns a rank to each row within the partition, with no gaps in the ranking values.

Sample Data:

Before

department_id first_name salary
3 Frank 123000
2 Jane 135000
3 Ashley 115000
NULL Glenn 115000
2 Kelly 125000
1 Richard 120000
1 George 105000
5 Kyle 200000
2 James 107000
1 Gustavo 100000

After

department_id first_name salary
NULL Glenn 115000
1 Richard 120000
1 George 105000
1 Gustavo 100000
2 Jane 135000
2 Kelly 125000
2 James 107000
3 Ashley 115000
3 Frank 123000
5 Kyle 200000

Example: Query without the WHERE Statement

In this example, we are returning the department_id, first_name, and salary column, along with a DENSE_RANK column to each employee within their department based on descending salary order. Richard is ranked number 1 because he has the highest salary in department_id 1 and Jane is is also ranked 1 because she has the highest salary in department_id 2.

Example: Query with the WHERE Statement

In this example, we are returning the department_id, first_name, and salary column of employees in department_id 1, along with a DENSE_RANK column to each employee within this department based on descending salary order. Richard is ranked number 1 because he has the highest salary in department_id 1 followed by George and then Gustavo.