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.
Try it on Neon!
Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.
Function signature
dense_rank() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)dense_rank example
Let’s say we have a student_scores table of students along with their name and score:
CREATE TABLE student_scores (
student_id SERIAL PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
score INT NOT NULL
);
INSERT INTO student_scores (student_name, score) VALUES
('Alice', 85),
('Bob', 92),
('Charlie', 78),
('David', 92),
('Eve', 85),
('Frank', 78);student_scores
| student_id | student_name | score |
|------------|--------------|-------|
| 1 | Alice | 85 |
| 2 | Bob | 92 |
| 3 | Charlie | 78 |
| 4 | David | 92 |
| 5 | Eve | 85 |
| 6 | Frank | 78 |You can use dense_rank to assign a rank to each row in the result set:
SELECT
student_id,
student_name,
score,
dense_rank() OVER (ORDER BY score DESC) AS rank
FROM
student_scores;This query returns the following values:
| student_id | student_name | score | rank |
|------------|--------------|-------|------|
| 2 | Bob | 92 | 1 |
| 4 | David | 92 | 1 |
| 1 | Alice | 85 | 2 |
| 5 | Eve | 85 | 2 |
| 3 | Charlie | 78 | 3 |
| 6 | Frank | 78 | 3 |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
CREATE TABLE student_scores_by_class (
student_id SERIAL PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
score INT NOT NULL,
class_id INT NOT NULL
);
INSERT INTO student_scores_by_class (student_name, score, class_id) VALUES
('Alice', 85, 1),
('Bob', 92, 1),
('Charlie', 78, 1),
('David', 92, 2),
('Eve', 85, 2),
('Frank', 78, 2);| student_id | student_name | score | class_id |
|------------|--------------|-------|----------|
| 1 | Alice | 85 | 1 |
| 2 | Bob | 92 | 1 |
| 3 | Charlie | 78 | 1 |
| 4 | David | 92 | 2 |
| 5 | Eve | 85 | 2 |
| 6 | Frank | 78 | 2 |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.
SELECT
student_id,
student_name,
score,
class_id,
dense_rank() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank_within_class
FROM
student_scores_by_class;This query returns the following values:
| student_id | student_name | score | class_id | rank_within_class |
|------------|--------------|-------|----------|-------------------|
| 2 | Bob | 92 | 1 | 1 |
| 1 | Alice | 85 | 1 | 2 |
| 3 | Charlie | 78 | 1 | 3 |
| 4 | David | 92 | 2 | 1 |
| 5 | Eve | 85 | 2 | 2 |
| 6 | Frank | 78 | 2 | 3 |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.
Filter dense_rank results in WHERE clause
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:
WITH RankedScores AS (
SELECT
student_id,
student_name,
score,
class_id,
dense_rank() OVER (PARTITION BY class_id ORDER BY score DESC) AS dense_rank
FROM
student_scores_by_class
)
SELECT
student_id,
student_name,
score,
class_id,
dense_rank
FROM
RankedScores
WHERE
dense_rank <= 2;This query returns the following values:
| student_id | student_name | score | class_id | dense_rank |
|------------|--------------|-------|----------|------------|
| 2 | Bob | 92 | 1 | 1 |
| 1 | Alice | 85 | 1 | 2 |
| 4 | David | 92 | 2 | 1 |
| 5 | Eve | 85 | 2 | 2 |Additional considerations
This section covers additional considerations for the dense_rank function.
How is dense_rank different from the rank function?
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.
SELECT
student_id,
student_name,
score,
rank() OVER (ORDER BY score DESC) AS rank
FROM
student_scores;This query returns the following values:
| student_id | student_name | score | rank |
|------------|--------------|-------|------|
| 2 | Bob | 92 | 1 |
| 4 | David | 92 | 1 |
| 1 | Alice | 85 | 3 |
| 5 | Eve | 85 | 3 |
| 3 | Charlie | 78 | 5 |
| 6 | Frank | 78 | 5 |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:
SELECT class_id, dense_rank() OVER (ORDER BY COUNT(*) DESC) AS student_count_rank, COUNT(*) AS student_count
FROM student_scores_by_class
GROUP BY class_id;This query returns the following values:
| class_id | student_count_rank | student_count |
|-----------|---------------------|---------------|
| 2 | 1 | 3 |
| 1 | 1 | 3 |Use with SUM:
SELECT class_id, dense_rank() OVER (ORDER BY SUM(score) DESC) AS total_score_rank, SUM(score) AS total_score
FROM student_scores_by_class
GROUP BY class_id;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:
| class_id | total_score_rank | total_score |
|-----------|-------------------|-------------|
| 2 | 1 | 255 |
| 1 | 1 | 255 |Use with AVG:
SELECT class_id, dense_rank() OVER (ORDER BY AVG(score) DESC) AS average_score_rank, AVG(score) AS average_score
FROM student_scores_by_class
GROUP BY class_id;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:
| class_id | average_score_rank | average_score |
|-----------|---------------------|---------------------|
| 2 | 1 | 85.0000000000000000 |
| 1 | 1 | 85.0000000000000000 |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