Postgres has a built-in Window function called RANK() that assigns a rank to every single row within a partition. For instance, in Postgres, the same rank is awarded to all rows that tie for a rank. However, the RANK() function allows us to rank the rows based on the provided columns instead of retrieving the consecutive integers. A frequent use case of the RANK() function is creating reports for the top N or bottom N records.

This post explains the usage of the RANK() function using practical examples.

How to Use the RANK() Function in PostgreSQL?

In PostgreSQL, the “RANK()” function is used with the “OVER” clause, as illustrated in the following syntax:

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

Here in this syntax:

- The “PARTITION BY” clause splits the records of the result set into partitions or groups and the RANK() function is applied to each partition.
- The “ORDER BY” clause sorts the rows of each partition(in ascending or descending order) to which the RANK() function is applied.

Let’s put this concept into practice for a deep understanding.

Example 1: How Does the RANK() Function Work in Postgres?

In this example, we will utilize a “programming_languages” table that we have already created in our database:

SELECT * FROM programming_languages;

In the following example, the RANK() function is used on the “programming_languages” to assign a rank to each row based on the “language” column:

SELECT language,
RANK () OVER ( 
ORDER BY language 
) 
FROM
programming_languages;

The output shows that instead of assigning a consecutive integer, a rank has been assigned to each row based on the “language” column.

Example 2: How Does the RANK() Function Work With the Postgres PARTITION BY Clause?

This example explains the usage of the RANK() function with the PARTITION BY clause:

SELECT *,
RANK () OVER ( 
PARTITION BY language
ORDER BY id
) 
FROM
programming_languages;

The output demonstrates that partitions have been created based on the “language” and a particular rank is assigned to each row of a partition.

Conclusion

Postgres has a built-in Window function called RANK() that assigns a rank to every single row within a partition. For instance, in Postgres, the same rank is awarded to all rows that tie for a rank. However, the RANK() function allows us to rank the rows based on the provided columns instead of retrieving the consecutive integers. This post explained the usage of the RANK() using appropriate examples.