Updated May 8, 2023

Introduction to PostgreSQL RANK()

The following article provides an outline of PostgreSQL RANK(). We can assign a rank to each row of the partition of a result set by using the RANK() function. The rank of the first row of a partition is 1. The rank is incremented in a fashion where the next row’s rank equals the number of rows tied to the rank. Since the values of the rank computation are determined internally by the OVER clause, no explicit parameters are required.

Syntax:

RANK() OVER (
[PARTITION BY partition_exp, .... ]
ORDER BY sort_exp [ASC | DESC], ....
)

Explanation:

PARTITION BY clause: The rows of the partitions of the result set to which the PostgreSQL RANK() function is applied.
ORDER BY clause: Defines the order of rows in each partition to which the PostgreSQL RANK() function is applied.

How RANK() Function works in PostgreSQL?

To calculate the rank of the next row, the PostgreSQL RANK function adds the number of rows to the position assigned to the previous rank.
Because of this, the rank might not be sequential.
All of the partition rows with the same values get the same rank.

Examples of PostgreSQL RANK()

Given below are the examples:

Now let’s create a new table of name ‘Grades’, which will have a column named’Grade’ using CREATE TABLE statement as follows:

Code:

CREATE TABLE Grades (
Grade VARCHAR(1)
);

Now, insert some data into the ‘Grades’ table using the INSERT statement as follows:

Code:

INSERT INTO Grades(Grade)
VALUES
('A'),('A'),('A'),
('B'),('B'),
('C'),
('D');

Illustrate the content of the Grades table with the help of the following snapshot and SQL statement.

Code:

SELECT
Grade
FROM
Grades;

Output:

Now, with the help of the RANK() Function, we can assign a rank to the row of the Grade column in the result set of the Grades table as follows:

Code:

SELECT
Grade,
RANK () OVER (
ORDER BY Grade
) grade_rank
FROM
Grades;

Output:

From the above snapshot, we can see that the first three rows have the rank assigned as rank 1, which is the same for all rows whose value is ‘A’.

The rank of the fourth and fifth rows is assigned to rank 4 because of the PostgreSQL.

RANK() function calculated it as the previous rank summed with a total number of rows tied to that rank. Similarly, it assigns rank 6 and ranks 7 to the sixth and seventh rows, respectively.

Now let’s create tables of name ‘transaction’ and ‘invoices’ in order to understand the RANK function with the PARTITION BY clause.

Let’s create tables using the CREATE TABLE statement as follows:

Code:

CREATE TABLE transaction (
transaction_id serial PRIMARY KEY,
transaction_data VARCHAR (256) NOT NULL
);

CREATE TABLE invoices (
invoice_id serial PRIMARY KEY,
transaction_id INT NOT NULL,
invoice_data VARCHAR (256) NOT NULL,
invoice_amount INT NOT NULL,
FOREIGN KEY (transaction_id) REFERENCES transaction (transaction_id)
);

Now insert some data in the transaction and invoices table by using the INSERT statement as follows:

Code:

INSERT INTO transaction (transaction_data)
VALUES
('Purchase of Mobile'),
('Purchase of PC'),
('Purchase of Headphone'),
('Purchase of Mouse'),
('Purchase of Cable');

INSERT INTO invoices (invoice_data, transaction_id,invoice_amount)
VALUES
('Purchase of Mobile', 1,30500),
('Purchase of Mobile', 1,30500),
('Purchase of Mobile', 1,20500),
('Purchase of PC', 2,15000),
('Purchase of PC', 2,12000);

Illustrate the result of the above statement with the help of the following snapshots and select statements:

Code:

select * from transaction;

Output:

Code:

select * from invoices;

Output:

Example #1

without PARTITION

This example assigns a rank to each row as per the transaction_id of invoices.

Code:

SELECT
invoice_id,
invoice_data,
invoice_amount,
RANK () OVER (
ORDER BY invoice_amount DESC
) invoice_amount_rank
FROM
invoices;

Output:

In the above example, the entire table is considered a single PARTITION as we have not defined the Partition BY clause.

Example #2

with PARTITION BY

Code:

SELECT
invoice_id,
invoice_data,
invoice_amount,
transaction_data,
RANK () OVER (
PARTITION BY i.transaction_id
ORDER BY invoice_amount DESC
) invoice_amount_rank
FROM
invoices i
INNER JOIN transaction t
ON i.transaction_id = t.transaction_id;

Output:

In the above example, we have defined the PARTITION BY clause. In PostgreSQL, you can use the PARTITION BY clause to group invoices into partitions based on the transaction_id column. Also, we have an ORDER BY clause defined, which sorts invoices from high to low in each partition by their invoice_amount.

Here, you can use the RANK function in PostgreSQL to assign a rank to each transaction data, which will reset when the transaction data changes. This allows for clear visualization of the assigned ranks for each invoice.

Conclusion

From the above article, we hope you understand how to use the PostgreSQL RANK function and how the PostgreSQL RANK() function works to assign the rank and fetch the data. Also, we have added some examples of the PostgreSQL RANK() Function to understand it in detail.