How to Use RANK() Function in PostgreSQL with Examples

In PostgreSQL, use the RANK() window function to assign a rank number to rows within a partition of a query result, ordered by specified columns. It returns the same rank for ties and skips ranks after ties. Use it with OVER() clause to define ordering and partitioning.

Syntax

The RANK() function assigns a rank to each row within a partition of the result set. The syntax is:

• RANK() OVER (PARTITION BY column1, column2 ORDER BY column3 DESC)

Explanation:

• RANK(): The ranking function.
• OVER(): Defines the window for ranking.
• PARTITION BY: Divides rows into groups to rank separately.
• ORDER BY: Defines the order to assign ranks.

sql

RANK() OVER (PARTITION BY partition_column ORDER BY order_column [ASC|DESC])

Example

This example shows how to rank employees by their salary within each department. Employees with the same salary get the same rank, and ranks skip numbers after ties.

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', 4500);

SELECT
  name,
  department,
  salary,
  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 |           3
 Eve    | HR         |   4500 |           1
 David  | HR         |   4000 |           2

Common Pitfalls

Common mistakes when using RANK() include:

• Not using OVER() clause, which causes syntax errors.
• Confusing RANK() with DENSE_RANK(): RANK() skips ranks after ties, while DENSE_RANK() does not.
• Forgetting to specify ORDER BY inside OVER(), which is required.

sql

/* Wrong: Missing OVER() */
SELECT name, RANK() FROM employees;

/* Right: Include OVER() with ORDER BY */
SELECT name, RANK() OVER (ORDER BY salary DESC) FROM employees;

Quick Reference

Function	Description
RANK()	Assigns rank with gaps after ties
DENSE_RANK()	Assigns rank without gaps after ties
ROW_NUMBER()	Assigns unique sequential number without ties
PARTITION BY	Divides rows into groups for ranking
ORDER BY	Defines order of ranking within partitions

Key Takeaways

Use RANK() with OVER() clause to assign ranks within partitions.

RANK() gives the same rank to ties and skips subsequent ranks.

Always include ORDER BY inside OVER() to define ranking order.

Use PARTITION BY to rank rows separately by groups.

For no gaps in ranking, consider using DENSE_RANK() instead.