# Postgres dense_rank() function

Returns the rank of the current row without gaps

You can use the `dense_rank`

function to assign a rank to each distinct row within a result set. It provides a non-gapped ranking of values which is particularly useful when dealing with datasets where ties need to be acknowledged without leaving gaps in the ranking sequence.

## Function signature

`dense_rank`

example

Letâ€™s say we have a `student_scores`

table of students along with their name and score:

**student_scores**

You can use `dense_rank`

to assign a rank to each row in the result set:

This query returns the following values:

## Advanced examples

This section shows advanced usage examples for the `dense_rank`

function.

`dense_rank`

with `PARTITION BY`

and `ORDER BY`

clause

Let's modify the previous example to include a `class_id`

column to represent different classes:

**student_scores_by_class**

The `PARTITION BY`

clause below is used in conjunction with ranking function to divide the result set into partitions based on one or more columns. Within each partition, the ranking function operates independently.

This query returns the following values:

This partitions the result set into two groups based on the `class_id`

column, and the ranking is performed independently within each class. As a result, students are ranked within their respective classes, and the ranking starts fresh for each class.

`dense_rank`

results in `WHERE`

clause

Filter To filter on `dense_rank`

results in a `WHERE`

clause, move the function into a common table expression (CTE).

Let's say you want to find the dense rank for the top two scores within each class:

This query returns the following values:

## Additional considerations

This section covers additional considerations for the `dense_rank`

function.

`dense_rank`

different from the `rank`

function?

How is The `rank`

function assigns a unique rank to each distinct row in the result set and leaves gaps in the ranking sequence when there are ties.
If two or more rows have the same values and are assigned the same rank, the next rank will be skipped.

This query returns the following values:

Alice and Eve, who share the second-highest score, have ranks 3 and 5, and there is a gap in the ranking sequence. When using `dense_rank`

, Alice and Eve, who share the second-highest score, both have a rank of 2, and there is no gap in the ranking sequence.

### Aggregations

You can combine `dense_rank`

with other functions like `COUNT`

, `SUM`

, `AVG`

for aggregations.

Use with `COUNT`

:

This query returns the following values:

Use with `SUM`

:

This query ranks the classes based on their total scores, assigning the highest rank to the class with the highest total score.

This query returns the following values:

Use with `AVG`

:

This query ranks the classes based on their average scores, assigning the highest rank to the class with the highest average score.

This query returns the following values:

### Indexing

Creating indexes on the columns specified in the `ORDER BY`

(sorting) and `PARTITION BY`

(partitioning) clauses can significantly improve performance. In this case, queries on the `student_scores`

table would benefit from creating indexes on `class_id`

and `score`

columns.

## Resources

Last updated on