How to Use ROW_NUMBER() in PostgreSQL

How do I generate row numbers in PostgreSQL?

ROW_NUMBER() assigns a unique, sequential number to each row—optionally restarting numbering for each partition—based on an ORDER BY clause.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is PostgreSQL ROW_NUMBER() used for?

ROW_NUMBER() adds an ordinal position to each returned row, making it easy to reference the first, last, or nth row after ordering. Because it’s a window function, it never collapses rows like aggregate functions do.

What is the exact syntax of ROW_NUMBER()?

Use the function inside a SELECT list and pair it with the OVER clause. You must supply ORDER BY inside OVER; PARTITION BY is optional when you want numbering to restart per group.

SELECT ROW_NUMBER() OVER (PARTITION BY partition_expr ORDER BY sort_expr) AS row_num FROM table_name;

How do I add a simple row number to all rows?

Select the table, call ROW_NUMBER(), and order by the desired column(s). The result set will carry an extra column with incremental numbers starting at 1.

SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn, * FROM employees;

How can I restart row numbers for each group?

Include PARTITION BY. PostgreSQL begins counting from 1 every time the partition key changes.

SELECT department_id, employee_name, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) AS dept_rank FROM employees;

How can I filter by row number (e.g., top N rows)?

Wrap the query or use a CTE, then filter on the generated row number.

WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY total_sales DESC) AS rn FROM sales ) SELECT * FROM ranked WHERE rn <= 3;

What are best practices for ROW_NUMBER()?

Always ORDER BY deterministic columns to avoid unpredictable numbering. Index columns used in ORDER BY for performance. Prefer ROW_NUMBER() over LIMIT/OFFSET when you also need ranking.

Common mistakes with ROW_NUMBER()

Ignoring ORDER BY: Without a clear sort, numbers depend on planner whims. Always specify ORDER BY.
Filtering inside the same SELECT: You can’t reference an alias in the WHERE clause of the same level; filter in an outer query or CTE.

Additional FAQs

Does ROW_NUMBER() require a unique ORDER BY?

No, but duplicate sort values lead to arbitrary ordering for ties. Add a tiebreaker column when order matters.

Is ROW_NUMBER() slower than LIMIT/OFFSET?

It can be, because the window must process every qualifying row. Add proper indexes to mitigate.

Can I make numbering start at 0?

Yes—subtract 1 in the SELECT: ROW_NUMBER() - 1 AS rn0.

Why How to Use ROW_NUMBER() in PostgreSQL is important

How to Use ROW_NUMBER() in PostgreSQL Example Usage


SELECT department_id, employee_name,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM   employees;

How to Use ROW_NUMBER() in PostgreSQL Syntax


ROW_NUMBER() OVER ( [PARTITION BY partition_expression] ORDER BY sort_expression [ASC|DESC] [NULLS FIRST|LAST] )

Common Mistakes

Omitting ORDER BY inside OVER causes non-deterministic numbering. Always specify ORDER BY so results remain stable across runs.
Filtering by the row number alias in the same SELECT level: WHERE rn <= 3 fails. Wrap the query in a subquery or CTE, then filter.