Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-rank-function/ below:

PostgreSQL - RANK Function - GeeksforGeeks

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.

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], ...
)

• 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.

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

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

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.

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.

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

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:

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.

• 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.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4