PostgreSQL – RANK Function

Last Updated : 01 Nov, 2024

The RANK() function in PostgreSQL is an advanced analytical tool used to assign a rank to each row within a specific partition in a result set. This function is especially beneficial for ranking items based on specified criteria, making it ideal for data analysis and reporting tasks like identifying top performers or sorting items by highest values.

The rank assigned to each row starts from 1 in each partition and increases based on the ORDER BY clause. If two rows have the same value, they receive the same rank, but the following rank will be skipped. In this article, we’ll explain the syntax, use cases, and detailed examples of the PostgreSQL RANK() function.

what is the PostgreSQL RANK Function?

The RANK() function assigns a unique rank to each row in a result set. Ranking starts at 1 for each partition and increases based on the ORDER BY clause. If multiple rows share the same value, they receive the same rank, causing a “gap” in the rank sequence. This can be helpful in scenarios where we want to see a leaderboard-style ranking.

Syntax

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

Key Terms

PARTITION BY Clause: Divides the result set into partitions where the RANK() Function is applied independently.
ORDER BY Clause: Determines the order in which the rows in each partition are ranked.

Examples of PostgreSQL RANK Function

Let us look at practical examples of the RANK() function in PostgreSQL to better understand how it works.

Example 1: Ranking a Simple List

In this example, we create a table called Rankings with a single column of names. We then apply the RANK() function to assign ranks to each name in alphabetical order.

Query:

CREATE TABLE Rankings(
    g VARCHAR(100)
);

Insert some data into the ‘Rankings' table:

INSERT INTO Rankings(g)
VALUES('Ram'), ('Ravi'), ('Bhola'), ('Bhagat'), ('Bhushan'), ('Chintu'), ('Isha');

Now, use the ‘RANK()‘ function to assign ranks to the rows in the result set of ranks table:

SELECT
    g,
    RANK() OVER ( 
        ORDER BY g 
    ) rank
FROM
    Rankings;

Output

Example 1: Output

Explanation:

In this query, we rank each entry in the Rankings table alphabetically. Since there is no PARTITION BY clause, the entire result set is treated as one partition, and ranks are assigned based on alphabetical order.

Example 2: Ranking within Groups

In this example, we have two tables: Animal_groups and Mammals. Each animal belongs to a different group (e.g., terrestrial, aquatic, or winged). We’ll use the RANK() function to rank each mammal by lifespan within its group.

`Animal_groups`

CREATE TABLE Animal_groups (
    animal_id serial PRIMARY KEY,
    animal_name VARCHAR (255) NOT NULL
);

INSERT INTO Animal_groups (animal_name)
VALUES
    ('Terrestrial'),
    ('Aquatic'),
    ('Winged');

`Mammals` tables

CREATE TABLE Mammals (
    mammal_id serial PRIMARY KEY,
    mammal_name VARCHAR (255) NOT NULL,
    lifespan DECIMAL (11, 2),
    animal_id INT NOT NULL,
    FOREIGN KEY (animal_id) REFERENCES Animal_groups (animal_id)
);

INSERT INTO Mammals(mammal_name, animal_id, lifespan)
VALUES
    ('Cow', 1, 10),
    ('Dog', 1, 7),
    ('Ox', 1, 13),
    ('Wolf', 1, 11),
    ('Blue Whale', 2, 80),
    ('Dolphin', 2, 5),
    ('Sea Horse', 2, 3),
    ('Octopus', 2, 8),
    ('Bat', 3, 4),
    ('Flying Squirrels', 3, 1),
    ('Petaurus', 3, 2);

The below statement uses the RANK() function to assign a rank to each mammals by its lifespan:

SELECT
    mammal_id,
    mammal_name,
    lifespan,
    RANK () OVER ( 
        ORDER BY lifespan DESC
    ) long_life 
FROM
    Mammals;

Output:

Example 2: Output

Explanation:

Here, we use PARTITION BY animal_id to rank each mammal by lifespan within its respective group (terrestrial, aquatic, winged). ORDER BY lifespan DESC ranks mammals from longest to shortest lifespan.

Important Points About RANK() Function in PostgreSQL

The RANK() function assigns a rank to each row within a result set based on the ordering specified in the ORDER BY clause.
We can partition the result set using the PARTITION BY clause, which divides the result set into partitions.
Like SQL Server and other relational databases, RANK() is a standard SQL function supported by PostgreSQL.
RANK() Function in PostgreSQL is commonly used for analytical tasks such as generating top-N lists, creating leaderboards, and identifying ranks within data partitions.

Conclusion

The RANK() function in PostgreSQL is a powerful analytical function ideal for ranking rows based on specific conditions, whether for top-N analysis, leaderboards, or identifying ranks within data partitions. By understanding and utilizing the PARTITION BY and ORDER BY clauses, you can efficiently group and rank data, enhancing data analysis and reporting capabilities.

FAQs

What is the RANK() function in PostgreSQL?

The RANK() function in PostgreSQL is used to assign a rank to each row within a query result set, starting from 1 within each partition if a PARTITION BY clause is used.

What is the difference between RANK() and DENSE_RANK()?

The RANK() function skips ranks when there are ties, meaning if two rows share the same rank, the following rank is skipped. DENSE_RANK(), on the other hand, does not skip ranks and assigns consecutive ranks even when there are ties.

What does RANK() do in SQL?

The RANK() function in SQL assigns a ranking to each row in a partition based on the specified ordering. It’s particularly useful for ranking and sorting data.

PostgreSQL - DENSE_RANK() Function

RajuKumar19

Improve

Article Tags :