How to Use ROW_NUMBER in PostgreSQL

How do I use the ROW_NUMBER window function in PostgreSQL?

ROW_NUMBER assigns a unique sequential number to each row within a result-set partition, ordered by the columns you specify.

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

Why choose ROW_NUMBER instead of SERIAL?

ROW_NUMBER is calculated on the fly inside a SELECT, UPDATE, or INSERT … SELECT. It does not create a physical column or require a sequence, making it perfect for ad-hoc ranking, pagination, and de-duplicating query results.

How does the ROW_NUMBER function work?

PostgreSQL evaluates the OVER() clause after the WHERE and GROUP BY stages. It partitions the rows (optional), orders them, and then assigns consecutive integers starting at 1 within each partition.

What is the basic syntax?

The minimal pattern is ROW_NUMBER() OVER (ORDER BY column). Add PARTITION BY to reset the counter per logical group, such as each customer.

How can I paginate ecommerce orders?

Wrap your query in a CTE, add ROW_NUMBER, and filter by the desired page range. This prevents OFFSET from scanning unwanted rows and keeps performance predictable.

How do I remove duplicate customers by email?

Use ROW_NUMBER in a subquery ordered by created_at DESC, keep rows where row_num = 1, and DELETE the rest. This keeps the earliest or latest row per email.

Can I update rows using ROW_NUMBER?

Yes. PostgreSQL allows UPDATE … FROM. Compute ROW_NUMBER in a subquery, JOIN it, and update only rows with a specific row number, such as setting a preferred flag on the first product per category.

What performance tips apply?

Create indexes that match the PARTITION BY and ORDER BY columns. Avoid unnecessary ORDER BY expressions and limit partitions when possible to reduce memory usage during sorting.

Does ROW_NUMBER replace LIMIT/OFFSET?

Not always. LIMIT/OFFSET is simpler for one-time queries, but ROW_NUMBER enables stable, keyset-style pagination and lets you retrieve total counts without an extra query.

Why How to Use ROW_NUMBER in PostgreSQL is important

How to Use ROW_NUMBER in PostgreSQL Example Usage


WITH ranked_orders AS (
    SELECT
        o.id,
        o.customer_id,
        o.order_date,
        o.total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id
            ORDER BY o.order_date DESC
        ) AS row_num
    FROM orders o
)
SELECT *
FROM ranked_orders
WHERE row_num = 1; -- Latest order per customer

How to Use ROW_NUMBER in PostgreSQL Syntax


SELECT
    ROW_NUMBER() OVER (
        PARTITION BY customers.id -- optional
        ORDER BY orders.order_date DESC
    ) AS row_num,
    customers.name,
    orders.total_amount
FROM customers
JOIN orders ON orders.customer_id = customers.id;

Common Mistakes

Ordering by non-unique columns only. Ties can create non-deterministic numbering. Fix this by adding a tiebreaker column, such as the primary key, to the ORDER BY list.
Forgetting PARTITION BY when you need independent counters per group. This returns a global sequence instead of per-customer or per-category numbers. Always add PARTITION BY when logical separation is required.