Course contentsShow
← Data Science
Lesson 1007 of 2,14526. Advanced SQLFree lesson

ROW_NUMBER(): Assigning Unique Row Numbers

Using ROW_NUMBER() to assign sequential integers to rows within partitions, ordered by specified columns.

ROW_NUMBER(): Assigning Unique Row Numbers

You'll learn how to assign sequential integers to rows within partitions using ROW_NUMBER().

What is ROW_NUMBER()?

ROW_NUMBER() is a window function that assigns a unique, sequential integer to each row within a result set or partition, starting at 1. Think of it like numbering pages in a book—each page gets exactly one number, and the numbering follows a specific order you define.

Core Syntax

ROW_NUMBER() OVER (
    PARTITION BY column1, column2
    ORDER BY column3
)

The PARTITION BY clause divides your data into groups (optional), and ORDER BY determines the sequence within each partition. Without PARTITION BY, the entire result set is treated as one group.

How It Works

Without partitions:

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

This assigns 1 to the highest-paid employee, 2 to the next, and so on.

With partitions:

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

Now numbering restarts at 1 for each department. The highest-paid employee in Sales gets 1, and the highest-paid in Marketing also gets 1.

Key Characteristics

  • Always unique: Unlike RANK() or DENSE_RANK(), ties receive different numbers based on arbitrary order
  • Sequential: No gaps in numbering (always 1, 2, 3...)
  • Deterministic with ORDER BY: The same query produces the same numbering

Key Takeaway: ROW_NUMBER() assigns unique, sequential integers to rows within partitions based on your specified ordering, making it perfect for pagination, deduplication, and ranking scenarios where you need exactly one number per row.