This tutorial explores on how to work with the PostgreSQL functions. We also explore the function syntax and parameters and look at some practical examples.
PostgreSQL Rank() Function
The following shows the syntax of the rank() function in PostgreSQL:
We start by calling the rank() function. We then use the OVER keyword to indicate that we wish to perform a window function operation.
Next is the PARTITION BY partition_expression. This clause divides the rows into various partitions based on a given expression. The ranking is done independently in each partition where the rank integer value starts at 1 for each new partition.
Finally, we have the ORDER BY clause which specifies the column or expression that determines the row order.
PostgreSQL Rank() Function Example
The following query demonstrates a basic example of using PostgreSQL’s rank() function:
FROM (
VALUES (10), (20), (5), (15), (10)
) AS data(value);
In this example, we rank the values based on their ascending order. The two occurrences of the value are assigned with the same rank as they are similar and appear in the same position within the order.
The resulting table is as follows:
FROM (
VALUES (10), (20), (5), (15), (10)
) AS data(value);
PostgreSQL Rank() Function Example with the Partition By Clause
Consider the following example:
FROM (
VALUES (10), (20), (5), (15), (10)
) AS data(value);
In this example, we added the PARTITION BY clause with an expression that checks whether the value is an even or odd number. This should effectively group the values into two partitions: one for even numbers (where the remainder is 0) and the other for odd numbers (where the remainder is 1).
The resulting set is as follows:
PostgreSQL Rank() Function Example by Combining Multiple SQL Features
You can also create more complex queries using the rank() function. For example, take the Pagila database. Suppose we wish to fetch the top five customers based on their total rental payments and assign ranks to them.
We can use the rank() function as shown in the following query:
RANK() OVER (ORDER BY total_payments DESC) AS rank
FROM (
SELECT c.customer_id, c.first_name, c.last_name, SUM(p.amount) AS total_payments
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
) AS customer_payments
ORDER BY rank
LIMIT 10;
In this example, we select the customer_id, first_name, last_name, total_payment, and the assigned rank(). Then, we use the rank() function to assign a rank to each customer based on the total_payment that is sorted in descending order.
The inner subquery allows us to calculate the total payments for each customer by joining the customer and payment tables. We then group the result based on the customer_id, first_name, and last_name columns to fetch the sum of payments for each customer.
Finally, in the outer query, we apply the rank() function over the result set and order it by total_payments in ascending order. We also include the limit clause to fetch only the top 10 rows.
As you can see, you can generate more insightful data by combining multiple SQL features such as sorting, filtering, aggregates, joins, and more.
Conclusion
We explored how we can work with the rank() function in PostgreSQL to fetch the rank of a given row from a result set based on the defined conditions. We also covered how to combine the rank() function with other SQL tools to create more complex queries.
