PostgreSQL – RANK Function

ByAdmin

The RANK() function is used to assign a unique rank to each row within the result set based on the specified ordering criteria. This function is particularly useful when you want to find the relative position of rows in a sorted result set. It’s commonly used in scenarios like generating leaderboards, finding top performers, or identifying rankings in various contexts.

Here’s the basic syntax of the RANK() function:

RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression [ASC | DESC])
  • PARTITION BY: This is an optional clause that divides the result set into partitions or groups. The ranking is calculated separately within each partition. If you omit this clause, the ranking is calculated across the entire result set.
  • ORDER BY: This is where you specify the column(s) by which you want to order the result set. The ranking will be assigned based on this order.
  • ASC or DESC: This specifies whether the sorting should be in ascending (ASC) or descending (DESC) order.

Here’s a simple example of how you might use the RANK() function in PostgreSQL:

Suppose you have a table named “scores” with columns “player_name” and “score”, and you want to rank the players based on their scores:

SELECT player_name, score, RANK() OVER (ORDER BY score DESC) AS ranking FROM scores;

In this example, the RANK() function assigns a rank to each player based on their score in descending order. If multiple players have the same score, they will receive the same rank, and the next rank will be skipped. For example:

player_name | score | ranking

-------------+-------+---------

Player A | 100 | 1

Player B | 95 | 2

Player C | 90 | 3

Player D | 90 | 3

Player E | 85 | 5

The RANK() function does not give consecutive rankings when there are ties (rows with equal values), as shown in the example above. If you want consecutive rankings, you should the DENSE_RANK() function.

DENSE_RANK() OVER (ORDER BY score DESC) AS ranking

This function treats ties as a single rank and then continues with the next available rank.

Similar Posts