PostgreSQL – DENSE_RANK Function

ByAdmin

The DENSE_RANK() function is used to assign a unique rank to each row within the result set, similar to the RANK() function. However, unlike the RANK() function, the DENSE_RANK() function doesn’t leave gaps in ranking when there are ties (rows with equal values). Instead, it assigns the same rank to rows with the same values and then continues with the next available rank.

The syntax of the DENSE_RANK() function is very similar to that of the RANK() function:

DENSE_RANK() OVER (PARTITION BY partition_expression ORDER BY sort_expression [ASC | DESC])
  • PARTITION BY: Optional clause that divides the result set into partitions or groups. The ranking is calculated separately within each partition. If omitted, the ranking is calculated across the entire result set.
  • ORDER BY: Specifies the column(s) by which you want to order the result set for ranking.
  • ASC or DESC: Indicates whether the sorting should be in ascending (ASC) or descending (DESC) order.

Here’s a simple example of how to use the DENSE_RANK() function in PostgreSQL:

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

SELECT player_name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_ranking FROM scores;

In this example, the DENSE_RANK() function assigns dense ranks to players based on their scores in descending order. Unlike the RANK() function, if there are ties (players with the same score), they will receive the same rank, and the next rank will continue without gaps. For example:

player_name | score | dense_ranking

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

Player A | 100 | 1

Player B | 95 | 2

Player C | 90 | 3

Player D | 90 | 3

Player E | 85 | 4

As you can see, both “Player C” and “Player D” have the same score and share the rank 3, resulting in the next rank being 4 for “Player E”.

The DENSE_RANK() function is particularly useful when you want to provide consecutive rankings to items without leaving gaps, even in cases of tied values.

Similar Posts