ROW_NUMBER() Function in SQL

Introduction

Let’s say you have a list of items like a shopping list. The ROW_NUMBER function is similar to appending numbers to the list for each item in the order you jotted them down. This means you can refer to individual entries by their digit instead of going through them again.

In SQL, you work with data tables instead of a shopping list. ROW_NUMBER helps you add a numbering system to those tables, making it quicker to find and reference specific rows of data. ROW_NUMBER  returns the number of the current row within its partition. Rows numbers range from 1 to the number of partition rows.

Overview

• ROW_NUMBER in SQL assigns sequential numbers to rows in a table, helping quickly reference specific entries.
• The syntax for ROW_NUMBER includes the function itself, window specification, optional partitioning, and mandatory ordering.
• Examples illustrate ROW_NUMBER’s use for sequential numbering of rows with and without group partitioning.
• A step-by-step guide shows how to create a table, insert data, and apply ROW_NUMBER for sequential numbering in SQL.

Syntax of ROW_NUMBER() in SQL

The syntax for using the function is as follows:

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

Here is a breakdown of the components:

• ROW_NUMBER(): The function itself.
• OVER: Specifies the window function, which can include partitioning and ordering.
• PARTITION BY partition_expression: (Optional) Divides the result set into partitions to which the ROW_NUMBER() function is applied. The entire result set is treated as a single partition if not specified.
• ORDER BY sort_expression: This is required. It specifies the order in which the rows in each partition are numbered.

Working of ROW_NUMBER()

Partitioning: The PARTITION_BY clause lets you divide the data into partitions and assign separate sequences of row numbers within each partition. The entire result set is considered a single partition if you don’t specify partitions.

Ordering: The ORDER BY clause is crucial for the ROW_NUMBER function. It dictates the order in which the rows are numbered within each partition. The numbering starts from 1 and increases sequentially.

Now, let’s walk through the steps to create a table, insert some example data, and use the function to give sequential numbers to your data. We’ll cover two scenarios: numbering all rows sequentially and resetting the numbering within specific groups.

Let’s Create a Table

Here are the steps to create a table:

Step 1: Create the Table

We’ll create a table named students with columns for id, name, and department.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);

Step 2: Insert Sample Data

Next, we’ll insert some sample data into the students table.

INSERT INTO students (id, name, department) VALUES
(1, 'Alice', 'Engineering'),
(2, 'Bob', 'Engineering'),
(3, 'Charlie', 'Science'),
(4, 'David', 'Science'),
(5, 'Eve', 'Engineering'),
(6, 'Frank', 'Arts'),
(7, 'Grace', 'Arts');
#import csv

Step 3: Using the ROW_NUMBER() window Function Without Partition

First, we’ll use the ROW_NUMBER() function to give each student a sequential number arranged by their name.

SELECT
    id,
    name,
    department,
    ROW_NUMBER() OVER (ORDER BY name) AS row_num
FROM
    students;
#import csv

Output Without Partitioning

Step 4: Use the ROW_NUMBER() Function With Partitioning

Now, let’s reset the row number for each department, ordered by student name within each department.

SELECT
    id,
    name,
    department,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY name) AS row_num
FROM
    students;
#import csv

Output With Partitioning

Key Points:

• ROW_NUMBER assigns unique numbers even for rows with identical values.
• Unlike the RANK function, ROW_NUMBER doesn’t assign the same rank to rows with equal values.
• ROW_NUMBER is a temporary value calculated during query execution. To permanently store row numbers, consider using IDENTITY columns or sequences.

Common Use Cases

• Adding row numbers for reporting: This helps improve readability, especially when sorting by non-displayed criteria.
• Data pagination: ROW_NUMBER can be used to retrieve specific page sets of data.
• Ranking within groups: By partitioning and ordering the data, you can assign ranks to rows within specific groups.

Conclusion 

The ROW_NUMBER function in SQL is a powerful tool for adding sequential numbering to rows in a data table, enhancing data organization and accessibility. By understanding its syntax and components, such as the OVER, PARTITION BY, and ORDER BY clauses, users can effectively employ this function to create ordered sequences within entire datasets or specific partitions.

Practical examples and step-by-step guides illustrate how to implement ROW_NUMBER both with and without partitioning, demonstrating its versatility in various use cases, from improving report readability to enabling efficient data pagination and ranking within groups. Understanding and utilizing the function can greatly enhance the efficiency and clarity of data management tasks in SQL.

Frequently Asked Questions

Abhishek Kumar 13 Jun, 2024

Hello, I'm Abhishek, a Data Engineer Trainee at Analytics Vidhya. I'm passionate about data engineering and video games I have experience in Apache Hadoop, AWS, and SQL,and I keep on exploring their intricacies and optimizing data workflows :)