Introduction to PostgreSQL RANK() function

TheRANK() function assigns a rank to every row within a partition of a result set.

For each partition, the rank of the first row is 1. TheRANK() function adds the number of tied rows to the tied rank to calculate the rank of the next row, so the ranks may not be sequential. In addition, rows with the same values will get the same rank.

The following illustrates the syntax of theRANK() function:

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

In this syntax:

• First, thePARTITION BY clause distributes rows of the result set into partitions to which theRANK() function is applied.
• Then, theORDER BY clause specifies the order of rows in each a partition to which the function is applied.

TheRANK() function can be useful for creating top-N and bottom-N reports.

PostgreSQL RANK() function demo

First,create a new table namedranks that contains one column:

CREATE TABLE ranks (c VARCHAR(10));

Second,insert some rows into theranks table:

INSERT INTO ranks(c)VALUES('A'),('A'),('B'),('B'),('B'),('C'),('E');

Third, query data from theranks table:

SELECTcFROMranks;

Fourth, use theRANK() function to assign ranks to the rows in the result set ofranks table:

SELECTc,RANK () OVER (ORDER BY c) rank_numberFROMranks;

The following picture shows the output:

As you can see clearly from the output:

• The first and second rows receive the same rank because they have the same valueA.
• The third, fourth, and fifth rows receive the rank 3 because theRANK() function skips the rank 2 and all of them have the same valuesB.

PostgreSQL RANK() function examples

We’ll use theproducts table to demonstrate theRANK() function:

This picture shows the data of theproducts table:

1) Using PostgreSQL RANK() function for the whole result set

This example uses theRANK() function to assign a rank to each product by its price:

SELECTproduct_id,product_name,price,RANK () OVER (ORDER BY price DESC) price_rankFROMproducts;

In this example, we omitted thePARTITION BY clause, therefore, theRANK() function treated the whole result set as a single partition.

TheRANK() function calculated a rank for each row within the whole result set sorted by prices from high to low.

2) Using PostgreSQL RANK() function with PARTITION BY clause example

The following example uses theRANK() function to assign a rank to every product in each product group:

SELECTproduct_id,product_name,group_name,price,RANK () OVER (PARTITION BY p.group_idORDER BY price DESC) price_rankFROMproducts pINNER JOIN product_groups gON g.group_id = p.group_id;

In this example:

• First, thePARTITION BY clause distributes products into partitions grouped by product group id (group_id).
• Second, theORDER BY clause sort products in each partition by their prices from high to low.

TheRANK() function was applied to every product in each product group and it is reinitialized when the product group changed.

In this tutorial, you have learned how to use the PostgreSQLRANK() function to calculate a rank for every row in a partition of a result set.

Last updated onJuly 10, 2020

On this page

• Introduction to PostgreSQL RANK() function
• PostgreSQL RANK() function demo
• PostgreSQL RANK() function examples