Introduction to PostgreSQL RANK()
The following article provides an outline on 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 is equal to the number of rows tied to the rank. The rank calculation does not need any explicit parameter as its values are calculated internally using the OVER clause.
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?
- In order to calculate the rank of the next row, the PostgreSQL, the RANK function adds the number of rows to the rank assigned on the previous rank.
- Because of this, the rank might not be sequential.
- All of the rows of the partition 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 Grades table as follows:
Code:
SELECT
Grade,
RANK () OVER (
ORDER BY Grade
) grade_rank
FROM
Grades;
Output:
Here from the above snapshot, we can see that the first three rows have the rank assigned as rank 1, which 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 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 as 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, you can see we have defined PARTITION BY clause. The PARTITION BY clause splits the invoices into partitions which are grouped by the transaction_id column. Also, we have an ORDER BY clause defined, which sorts invoices high to low in each partition by their invoice_amount.
Here you can clearly visualize the invoices have assigned with rank using the RANK function for each transaction data, and it is again reinitialized when transaction data changed.
Conclusion
We hope from the above article you have understood 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 details.
Recommended Articles
This is a guide to PostgreSQL RANK(). Here we discuss the introduction, how the rank() function works in PostgreSQL, along with respective examples. You may also have a look at the following articles to learn more –
