How to Use ROW_NUMBER in PostgreSQL

How do I assign row numbers to query results in PostgreSQL?

ROW_NUMBER returns a sequential integer for each row in a result set based on the ORDER BY of a window.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does ROW_NUMBER do?

ROW_NUMBER() assigns a unique, sequential integer to each row returned by a query. Numbers restart only when PARTITION BY is specified; otherwise, numbering is global.

When should I use ROW_NUMBER()?

Use it to paginate results, pick the first row per group, de-duplicate data, or build rankings without adding surrogate keys.

How do I write the basic syntax?

Call ROW_NUMBER() OVER(ORDER BY column) in the SELECT list.Always include ORDER BY inside OVER to guarantee consistent numbering.

How can I restart numbering per group?

Add PARTITION BY group_column inside the OVER clause. Each partition starts at 1, letting you rank rows within categories like customers or products.

How do I filter to the first row in each group?

Wrap the query using a CTE or sub-query, then filter on WHERE row_num = 1.This keeps only the top-ranked row per partition.

Example: pick each customer’s latest order

This query numbers orders per customer by order_date DESC, then selects row_num = 1 to get the most recent order per customer.

Best practices for ROW_NUMBER()

1) Always ORDER BY deterministic columns.
2) Prefer PARTITION BY when grouping.
3) Index the ORDER BY columns for large datasets.

What are common mistakes?

1) Omitting ORDER BY causes non-deterministic numbering.
2) Confusing FILTER clause with PARTITION BY; they serve different purposes.

Why How to Use ROW_NUMBER in PostgreSQL is important

How to Use ROW_NUMBER in PostgreSQL Example Usage


WITH numbered 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 numbered
WHERE row_num = 1;

How to Use ROW_NUMBER in PostgreSQL Syntax


SELECT
    ROW_NUMBER() OVER (
        [PARTITION BY partition_expression]
        ORDER BY sort_expression [ASC|DESC]
    ) AS row_num,
    other_columns
FROM table_name;

-- Ecommerce example: rank newest orders per customer
SELECT
    id,
    customer_id,
    order_date,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
    ) AS row_num
FROM Orders;

Common Mistakes

Mistake: Leaving out ORDER BY in the OVER clause. Without ORDER BY, PostgreSQL may choose any order, producing inconsistent row numbers. Fix: Always specify ORDER BY with deterministic columns.
Mistake: Expecting PARTITION BY to sort rows. PARTITION BY only groups data; ORDER BY still controls ranking order. Fix: Provide ORDER BY even when PARTITION BY is used.