SQL DENSE_RANK() Function

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

Introduction to SQL DENSE_RANK() function #

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

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

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

Here’s the syntax of the DENSE_RANK() window function:

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

In this syntax:

The PARTITION BY clause divides the result set into partitions.
The ORDER BY specifies the order of rows in each partition.
The DENSE_RANK() function is applied to the rows in the specified order of each partition. It resets the rank value when the partition boundary is crossed.

Basic DENSE_RANK function example #

First, create a new table dense_rank_demos with one column v:

CREATE TABLE IF NOT EXISTS dense_rank_demos (v VARCHAR);Code language: SQL (Structured Query Language) (sql)

Try it

Second, insert some rows into the dense_rank_demos table:

INSERT INTO
  dense_rank_demos (v)
VALUES
  ('A'),
  ('B'),
  ('B'),
  ('C'),
  ('D'),
  ('D'),
  ('E');Code language: SQL (Structured Query Language) (sql)

Try it

Third, retrieve data from the dense_rank_demos table:

SELECT v FROM dense_rank_demos;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 v
---
 A
 B
 B
 C
 D
 D
 ECode language: SQL (Structured Query Language) (sql)

Finally, use the DENSE_RANK() and RANK() functions to assign ranks 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
  dense_rank_demos;Code language: SQL (Structured Query Language) (sql)

Output:

 v | my_dense_rank | my_rank
---+---------------+---------
 A |             1 |       1
 B |             2 |       2
 B |             2 |       2
 C |             3 |       4
 D |             4 |       5
 D |             4 |       5
 E |             5 |       7Code language: SQL (Structured Query Language) (sql)

Using SQL DENSE_RANK() over the result set example #

We’ll use the employees and departments tables from the sample database for the demonstration purposes:

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

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

Try it

Output:

 first_name  |  last_name  |  salary  | salary_rank
-------------+-------------+----------+-------------
 Steven      | King        | 24000.00 |           1
 Neena       | Kochhar     | 17000.00 |           2
 Lex         | De Haan     | 17000.00 |           2
 John        | Russell     | 14000.00 |           3
 Karen       | Partners    | 13500.00 |           4
 Michael     | Hartstein   | 13000.00 |           5
 Shelley     | Higgins     | 12000.00 |           6
 Nancy       | Greenberg   | 12000.00 |           6
 Den         | Raphaely    | 11000.00 |           7
...Code language: SQL (Structured Query Language) (sql)

In this example, the DENSE_RANK() function treats the whole result set as a single partition because we don’t use the PARTITION BY clause.

The ORDER BY clause sorts employee salaries from high to low and the DENSE_RANK() function assigns a rank to each employee based on the salary amount.

Using SQL DENSE_RANK() function over partition example #

The following statement uses the DENSE_RANK function to rank 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)

Try it

Output:

first_name  |  last_name  | department_name  |  salary  | salary_rank
-------------+-------------+------------------+----------+-------------
 Shelley     | Higgins     | Accounting       | 12000.00 |           1
 William     | Gietz       | Accounting       |  8300.00 |           2
 Jennifer    | Whalen      | Administration   |  4400.00 |           1
 Steven      | King        | Executive        | 24000.00 |           1
 Neena       | Kochhar     | Executive        | 17000.00 |           2
 Lex         | De Haan     | Executive        | 17000.00 |           2
 Nancy       | Greenberg   | Finance          | 12000.00 |           1
 Daniel      | Faviet      | Finance          |  9000.00 |           2
 John        | Chen        | Finance          |  8200.00 |           3
 Jose Manuel | Urman       | Finance          |  7800.00 |           4
 Ismael      | Sciarra     | Finance          |  7700.00 |           5
 Luis        | Popp        | Finance          |  6900.00 |           6
...Code language: SQL (Structured Query Language) (sql)

In this example:

First, the PARTITION BY clause divides the employees by departments into partitions.
Then, the ORDER BY clause sorts the employees in each department (partition) by their salaries.
Third, the DENSE_RANK() function assigns a rank to each row in each partition based on salary order.

If you want to find only employees who have the highest salary in their departments, you can 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)

Try it

Output:

 first_name | last_name | department_name  |  salary  | salary_rank
------------+-----------+------------------+----------+-------------
 Shelley    | Higgins   | Accounting       | 12000.00 |           1
 Jennifer   | Whalen    | Administration   |  4400.00 |           1
 Steven     | King      | Executive        | 24000.00 |           1
 Nancy      | Greenberg | Finance          | 12000.00 |           1
 Susan      | Mavris    | Human Resources  |  6500.00 |           1
 Alexander  | Hunold    | IT               |  9000.00 |           1
 Michael    | Hartstein | Marketing        | 13000.00 |           1
 Hermann    | Baer      | Public Relations | 10000.00 |           1
 Den        | Raphaely  | Purchasing       | 11000.00 |           1
 John       | Russell   | Sales            | 14000.00 |           1
 Adam       | Fripp     | Shipping         |  8200.00 |           1Code language: SQL (Structured Query Language) (sql)

Summary #

Use the SQL DENSE_RANK() function to rank rows in partitions without gaps in ranking values.

Databases #

Was this tutorial helpful ?