How to Use ROW_NUMBER() in PostgreSQL: Syntax and Examples

In PostgreSQL, use the ROW_NUMBER() window function to assign a unique sequential number to rows within a partition of a result set. It is used with OVER() clause, where you can specify partitioning and ordering to control numbering.

Syntax

The ROW_NUMBER() function assigns a unique number to each row in the result set. It is used with the OVER() clause, which can include:

• PARTITION BY: divides rows into groups to restart numbering for each group.
• ORDER BY: defines the order in which rows are numbered within each partition.

sql

ROW_NUMBER() OVER ([PARTITION BY column1, column2, ...] ORDER BY column3 [ASC|DESC], ...)

Example

This example shows how to assign row numbers to employees grouped by their department and ordered by their salary descending. It helps to find the highest paid employees per department.

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', 6000),
('Charlie', 'HR', 4500),
('David', 'HR', 4700),
('Eve', 'Sales', 5500);

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

Output

 name   | department | salary | rank 
--------+------------+--------+------
 David  | HR         |   4700 |    1
 Charlie| HR         |   4500 |    2
 Bob    | Sales      |   6000 |    1
 Eve    | Sales      |   5500 |    2
 Alice  | Sales      |   5000 |    3

Common Pitfalls

Common mistakes when using ROW_NUMBER() include:

• Not using ORDER BY inside OVER(), which leads to unpredictable row numbering.
• Confusing ROW_NUMBER() with RANK() or DENSE_RANK(), which handle ties differently.
• Forgetting to use PARTITION BY when you want numbering to restart for each group.

sql

/* Wrong: No ORDER BY, numbering is arbitrary */
SELECT name, ROW_NUMBER() OVER () AS rn FROM employees;

/* Right: Specify ORDER BY for consistent numbering */
SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;

Quick Reference

Clause	Description
ROW_NUMBER()	Assigns unique sequential number to rows.
OVER()	Defines window for numbering.
PARTITION BY	Groups rows to restart numbering per group.
ORDER BY	Specifies order of rows for numbering.

Key Takeaways

Use ROW_NUMBER() with OVER() to assign unique row numbers in PostgreSQL.

Always include ORDER BY inside OVER() for predictable numbering order.

Use PARTITION BY to restart numbering for each group of rows.

ROW_NUMBER() differs from RANK() and DENSE_RANK() in handling ties.

ROW_NUMBER() is useful for pagination, ranking, and filtering duplicates.