How to use Postgres Rank Function

In this PostgreSQL tutorial, I will teach how to use the Postgres Rank Function to assign the rank value to rows of the tables in a specific order.

Additionally, you will understand using the PARTITION BY clause with the RANK() function to assign the rank to each row within a partition.

How to use Postgres Rank Function

The RANK() function of PostgreSQL is a window function that assigns a number (which is called rank) to each row of the result set under a specific partition.

Within each partition, the rank() function assigns rank (1) to the first row. The rank() function assigns the same rank to the rows containing the same values.

The syntax is given below.

RANK() OVER (
    [PARTITION BY partitionExpression, ... ]
    ORDER BY sortExpression [ASC | DESC], ...
)
  • RANK(): This is the rank function itself.
  • OVER(): This represents that the rank function is performed over the specific window.
  • PARTITION BY: To divide the rows into partitions on the basis of the specific column, the rank() function assign a rank to rows of each partition separately. The PARTITION BY is optional.
  • ORDER BY: It is used to display the query result set in a specific order such as ascending and descending order and in which order the rank() function should be applied.

Let’s take a simple example and understand how the Postgres rank function works.

Suppose you have table ‘orders’ with columns first_name, orders_date, and total_items which you can see in the below picture.

How to use Postgres Rank Function Table Orders

The above table contains the order of the total items by each customer on a specific date. Now you want to assign a rank to each customer according to the total items. So use the below query.

SELECT first_name, total_items, rank() 
    OVER(ORDER by total_items)
FROM orders;
How to use Postgres Rank Function

If you look at the above query and its output, rank 1 is assigned to the customer ‘Thomas’, rank 2 to ‘James’, and so on. The rank is assigned in a specific order (ORDER by total_items), in ascending order based on the column ‘total_items’.

Also, the rank() function assigned the same rank to the customer ‘Danny’ and ‘Aurthor’ because they have ordered the same amount of items ( the total_items value is the same for both).

How to use Partition By Clause in Postgres Rank Function

The PARTITION BY clause is the optional clause in the Postgres rank function. When you use the PARTITION BY clause, it groups the rows based on the specific column. Then the rank() function assigns the rank to the rows of each partition separately.

If there are two partitions, then the Postgres rank function assigns the rank (1) to the first row of both partitions.

Let’s take an example where you have table ‘orders’ with customer_id, first_name, orders_date, and total_items which you can see in the below picture.

Partition By Clause in Postgres Rank Function Table Orders

Now you want to group the customer according to their id and assign the rank to each row of the groups. So use the below query.

SELECT customer_id, total_items, rank() 
    OVER(PARTITION BY customer_id
        ORDER by total_items)
FROM orders;
Partition By Clause in Postgres Rank Function

If you look at the output of the above query, the rows are partitioned according to the column customer_id which is indicated with different colors.

As I told you the rank() function assigns the rank to the rows of each partition separately and within each partition the first row has a ranking of 1.

Also, look for the customer_id equal to 3 where the ranking for both rows is 4 because they the have same value in the column ‘total_items’.

Conclusion

In this PostgreSQL tutorial, you learned how to use the PostgreSQL rank() function to assign the rank to the rows. Also learned how to use PARTITION BY clause to assign the rank to the rows within each partition.

You may also read: