Category: Window/Analytic Functions

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

ROW_NUMBER assigns a unique sequential integer to rows within a partition of a result set, starting at 1 for the first row in each partition. Unlike RANK and DENSE_RANK, ROW_NUMBER always produces unique values even when there are ties.

Syntax

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

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

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

Platform-Specific Notes

Snowflake:

• Fully supported

• Can be used with QUALIFY clause for filtering

• Deterministic ordering requires unique ORDER BY values

BigQuery:

• Fully supported

• Can be used with QUALIFY clause

• Works efficiently with partitioned tables

Databricks:

• Fully supported

• Standard Spark SQL implementation

• Cannot use QUALIFY (must use subquery)

Example 1: Ranking Sales by Amount

All Platforms:

SELECT 
    employee_name,
    sale_amount,
    sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_amount DESC) as overall_rank
FROM

SELECT 
    employee_name,
    sale_amount,
    sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_amount DESC) as overall_rank
FROM

SELECT 
    employee_name,
    sale_amount,
    sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_amount DESC) as overall_rank
FROM

Sample Data (sales table):

Result:

Note: Bob and Diana have the same sale_amount but get different row numbers (4 and 5) based on the secondary ordering.

Example 2: Row Number Within Partitions

All Platforms:

SELECT 
    department,
    employee_name,
    salary,
    hire_date,
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY salary DESC, hire_date ASC
    ) as dept_rank
FROM

SELECT 
    department,
    employee_name,
    salary,
    hire_date,
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY salary DESC, hire_date ASC
    ) as dept_rank
FROM

SELECT 
    department,
    employee_name,
    salary,
    hire_date,
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY salary DESC, hire_date ASC
    ) as dept_rank
FROM

Sample Data (employees table):

Result:

Example 3: Deduplication with QUALIFY (Snowflake/BigQuery)

Snowflake/BigQuery:

-- Keep only the most recent record per customer
SELECT 
    customer_id,
    email,
    updated_at
FROM customer_updates
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1

-- Keep only the most recent record per customer
SELECT 
    customer_id,
    email,
    updated_at
FROM customer_updates
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1

-- Keep only the most recent record per customer
SELECT 
    customer_id,
    email,
    updated_at
FROM customer_updates
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1

Databricks Alternative:

-- Databricks: use subquery
SELECT customer_id, email, updated_at
FROM (
    SELECT 
        customer_id,
        email,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) as rn
    FROM customer_updates
)
WHERE rn = 1

-- Databricks: use subquery
SELECT customer_id, email, updated_at
FROM (
    SELECT 
        customer_id,
        email,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) as rn
    FROM customer_updates
)
WHERE rn = 1

-- Databricks: use subquery
SELECT customer_id, email, updated_at
FROM (
    SELECT 
        customer_id,
        email,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) as rn
    FROM customer_updates
)
WHERE rn = 1

Sample Data (customer_updates table):

Result:

ROW_NUMBER vs RANK vs DENSE_RANK

Common Use Cases

• Deduplication: Remove duplicate records, keeping the most recent

• Pagination: Implement efficient page-based data retrieval

• Top N per group: Get top performers in each department/category

• Sequential numbering: Generate invoice numbers, ticket IDs

• Time-series analysis: Number events in chronological order

Performance Tips

• Use specific ORDER BY columns for deterministic results

• Combine with PARTITION BY to limit the scope of numbering

• In Snowflake/BigQuery, use QUALIFY instead of subqueries for better performance

• Index columns used in PARTITION BY and ORDER BY for better performance