How to Use ROW_NUMBER in Snowflake

Galaxy Glossary

How do I use the ROW_NUMBER window function in Snowflake?

ROW_NUMBER assigns a unique, sequential integer to each row within defined partitions of a Snowflake result set.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does ROW_NUMBER do in Snowflake?

ROW_NUMBER returns consecutive integers starting at 1 for each row, ordered within optional partitions.It’s ideal for ranking, de-duplicating, or pagination tasks.

How is ROW_NUMBER syntax structured?

The function is called inside a SELECT list and combined with the OVER() clause, which can include PARTITION BY and ORDER BY.

When should I partition the row numbers?

Use PARTITION BY when you need independent sequences per group, such as numbering orders per customer.

How do I rank customers’ latest orders?

Order rows inside the window by order_date DESC, then filter where row_num = 1 to isolate the latest record.

Can I paginate large result sets?

Yes.Number all rows, then filter by row number range (e.g., WHERE row_num BETWEEN 101 AND 200) to fetch page 2.

Best practices for ROW_NUMBER

Define a deterministic ORDER BY, index columns used in filtering, and avoid unnecessary partitions to keep queries fast.

.

Why How to Use ROW_NUMBER in Snowflake is important

How to Use ROW_NUMBER in Snowflake Example Usage


-- Fetch each customer’s most recent order
WITH ranked AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id
            ORDER BY o.order_date DESC
        ) AS rn
    FROM Orders o
)
SELECT id, customer_id, order_date, total_amount
FROM ranked
WHERE rn = 1;

How to Use ROW_NUMBER in Snowflake Syntax


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

-- Ecommerce example: list each customer’s orders, newest first
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;

Common Mistakes

Omitting ORDER BY in the OVER() clause. Without ORDER BY, Snowflake’s row numbering order is nondeterministic. Always specify a clear ORDER BY column list.
Filtering by ROW_NUMBER in the same SELECT without sub-querying. You must wrap the ROW_NUMBER logic in a CTE or subquery before applying WHERE rn = value, or Snowflake will error.

Frequently Asked Questions (FAQs)

Does ROW_NUMBER skip numbers when rows tie?

No. ROW_NUMBER always generates consecutive integers, even if ORDER BY columns contain duplicates. Use RANK() or DENSE_RANK() when ties matter.

Is ROW_NUMBER faster than LIMIT/OFFSET for pagination?

ROW_NUMBER with a subquery is often faster at large offsets because Snowflake can stop scanning once the upper bound is reached.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie