PostgreSQL – RANK Function

In PostgreSQL, the RANK() function is used to assign a rank to each row of the query result set within the specified partition. The rank of the first row within each partition is 1.

The following illustrates the syntax of the RANK() function:

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

Let’s analyze the above syntax:

• First, the PARTITION BY clause creates partition in the query result where the RANK() function is applied.
• The ORDER BY clause sets the order in which the query results are displayed.

Example 1:

First, create a table named Rankings that contains one column:

CREATE TABLE Rankings(
    g VARCHAR(100)
);

Now add some data to it:

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 2:

First, create two tables named Mammals and Animal_groups:

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

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)
);

Now add some data to it:

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

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: