PostgreSQL RANK Function

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

PostgreSQL RANK Function

The RANK() function in PostgreSQL is a window function that assigns a number, known as a rank, to each row of the result set within a specific partition.

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

Syntax

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 based on a specific column, the rank() function assigns 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.

Example 1: Basic Operation

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

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

The table above lists the total number of items ordered by each customer on a specific date. Now, you want to assign a rank to each customer based on the total number of items. So use the below query.

SELECT first_name, total_items, rank() 
    OVER(ORDER by total_items)
FROM orders;

After executing the above query, I got the expected output as shown in the screenshot below.

If you examine 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 customers ‘Danny’ and ‘Aurthor’ because they have ordered the same number of items ( the total_items value is the same for both).

Example 2: Using the Partition By Clause in the Postgres Rank Function

The PARTITION BY clause is an optional clause in the Postgres rank function. When you use the PARTITION BY clause, it groups the rows based on a 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 a table ‘orders’ with customer_id, first_name, order_date, and total_items, which you can see in the picture below.

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;

After executing the above query, I got the expected output as shown in the screenshot below.

If you examine the output of the above query, the rows are partitioned according to the customer_id column, which is indicated by 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 have the same value in the column ‘total_items’.

Example 3: Ranking Employees by Salary

We can execute the following query for this purpose.

SELECT 
    name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

After executing the above query, I got the expected output as shown in the screenshot below.

Example 4: Ranking Within Departments

We can use the following query for this purpose.

SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_salary_rank
FROM employees;

After executing the above query, I got the expected output as shown in the screenshot below.

Conclusion

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

You may also read:

• Postgresql row_number + Examples
• Postgresql generate_series with examples
• PostgreSQL Average Count
• What is PostgreSQL Good For
• PostgreSQL Compute Total Sum of Column
• PostgreSQL To_Char Date Format