How to Use dense_rank in PostgreSQL: Syntax and Examples

In PostgreSQL, use the dense_rank() window function to assign ranks to rows without gaps in ranking values when there are ties. It is used with an OVER() clause that defines the order and optional partitioning of rows.

Syntax

The dense_rank() function assigns a rank to each row within a partition of a result set, with no gaps in ranking values. It is used with the OVER() clause, which can include PARTITION BY to group rows and ORDER BY to define ranking order.

• dense_rank(): The function that calculates the rank.
• OVER(): Defines the window for ranking.
• PARTITION BY: (Optional) Divides rows into groups to rank separately.
• ORDER BY: Specifies the column(s) to rank by.

sql

dense_rank() OVER ([PARTITION BY partition_expression] ORDER BY order_expression)

Example

This example shows how to use dense_rank() to rank employees by their salary within each department. Employees with the same salary get the same rank, and ranks continue without gaps.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary INT
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 5000),
('Bob', 'Sales', 7000),
('Charlie', 'Sales', 7000),
('David', 'HR', 4000),
('Eve', 'HR', 4000),
('Frank', 'HR', 3000);

SELECT
  name,
  department,
  salary,
  dense_rank() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY department, salary_rank;

Output

 name   | department | salary | salary_rank 
--------+------------+--------+-------------
 Bob    | Sales      |   7000 |           1
 Charlie| Sales      |   7000 |           1
 Alice  | Sales      |   5000 |           2
 David  | HR         |   4000 |           1
 Eve    | HR         |   4000 |           1
 Frank  | HR         |   3000 |           2

Common Pitfalls

Common mistakes when using dense_rank() include:

• Forgetting the ORDER BY clause inside OVER(), which is required to define ranking order.
• Confusing dense_rank() with rank(): rank() leaves gaps in ranks when there are ties, but dense_rank() does not.
• Not using PARTITION BY when you want to rank within groups, causing ranking over the entire dataset instead.

Wrong example (missing ORDER BY):

sql

SELECT name, dense_rank() OVER () AS rank FROM employees;

Output

ERROR:  window function requires an ORDER BY clause

Quick Reference

Clause	Description
dense_rank()	Assigns ranks without gaps for ties
OVER()	Defines the window for ranking
PARTITION BY	Groups rows to rank separately (optional)
ORDER BY	Specifies the column(s) to rank by (required)

Key Takeaways

Use dense_rank() with OVER() and ORDER BY to assign ranks without gaps in PostgreSQL.

Include PARTITION BY to rank rows within groups separately.

dense_rank() assigns the same rank to ties and continues ranking without gaps.

Always include ORDER BY inside OVER() or you will get an error.

dense_rank() differs from rank() by not leaving gaps in ranking numbers.