PostgreSQL - DENSE_RANK Function

In PostgreSQL, the DENSE_RANK() function is a powerful tool used to assign ranks to rows within a partition of a result set, ensuring there are no gaps in the ranking values. Unlike the RANK() function, which may skip rank numbers when there are ties, DENSE_RANK() always returns consecutive rank values.

Let us explore the syntax, usage, and benefits of the DENSE_RANK() function with detailed examples.

What is the DENSE_RANK() Function in PostgreSQL?

The DENSE_RANK() function assigns a rank to each row in each partition of a result set. If rows have the same values, they receive the same rank. The ranking is always consecutive, making it different from the RANK() function.

Syntax

The following shows the syntax of the DENSE_RANK() function:

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

• PARTITION BY partition_expression: Divides the result set into partitions to which the DENSE_RANK() function is applied. This clause is optional; if omitted, the function treats the entire result set as a single partition.
• ORDER BY sort_expression [ASC | DESC]: Specifies the order of the rows within each partition. This determines the ranking.

How Does DENSE_RANK() Work?

The DENSE_RANK() function calculates the rank of each row within the partition defined by the PARTITION BY clause and ordered by the ORDER BY clause. The rank is reset when crossing the partition boundary.

PostgreSQL DENSE_RANK() Function Examples

Let's look into some practical examples to understand the DENSE_RANK() function better.

Example 1: Basic Usage with Simple Data

First, create a table named 'dense_ranks' that has one column:

CREATE TABLE dense_ranks (
    c VARCHAR(10)
);
INSERT INTO dense_ranks(c)
VALUES('A'), ('A'), ('B'), ('C'), ('C'), ('D'), ('E');
SELECT c from dense_ranks;

It will result in the below depiction:

Use the DENSE_RANK() function to assign a rank to each row in the result set:

SELECT c,
    DENSE_RANK() OVER (
        ORDER BY c
    ) dense_rank_number
FROM
    dense_ranks;

Output:

Example 2: Ranking Products by Price

First, create two tables named 'products' and 'product_groups' for the demonstration:

CREATE TABLE product_groups (
    group_id serial PRIMARY KEY,
    group_name VARCHAR (255) NOT NULL
);

CREATE TABLE products (
    product_id serial PRIMARY KEY,
    product_name VARCHAR (255) NOT NULL,
    price DECIMAL (11, 2),
    group_id INT NOT NULL,
    FOREIGN KEY (group_id) REFERENCES product_groups (group_id)
);
INSERT INTO product_groups (group_name)
VALUES
    ('Smartphone'),
    ('Laptop'),
    ('Tablet');

INSERT INTO products (product_name, group_id, price)
VALUES
    ('Microsoft Lumia', 1, 200),
    ('HTC One', 1, 400),
    ('Nexus', 1, 500),
    ('iPhone', 1, 900),
    ('HP Elite', 2, 1200),
    ('Lenovo Thinkpad', 2, 700),
    ('Sony VAIO', 2, 700),
    ('Dell Vostro', 2, 800),
    ('iPad', 3, 700),
    ('Kindle Fire', 3, 150),
    ('Samsung Galaxy Tab', 3, 200);
SELECT
    product_id,
    product_name,
    price,
    DENSE_RANK () OVER ( 
        ORDER BY price DESC
    ) price_rank 
FROM
    products;

Output:

Important Points About PostgreSQL DENSE_RANK() Function

• DENSE_RANK() always returns consecutive rank values, even when rows have the same values (ties).
• The rank is reset when it crosses the partition boundary.
• Unlike the RANK() function, DENSE_RANK() does not leave gaps in the ranking sequence.
• DENSE_RANK() partition the result set using the PARTITION BY clause. If not used, the entire result set is treated as a single partition.