ROW_NUMBER Function in SQL

Why Use ROW_NUMBER()?

ROW_NUMBER() comes in handy when you need to:

Order Data: It helps to order the data based on a particular column.

Remove Duplicates: It can be useful to delete duplicate rows from a table.

Pagination: It's often used in pagination scenarios in applications.

Syntax of ROW_NUMBER()

The syntax of ROW_NUMBER() is as follows:

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

The PARTITION BY clause is optional and divides the result set into partitions (or groups). The ROW_NUMBER() is reset for each partition. The ORDER BY clause defines the order of the rows in each partition.

Implementing ROW_NUMBER() in Real Scenarios

Let's see the ROW_NUMBER() function in action.

Example 1: Basic Usage

Suppose we have a 'Sales' table and we want to assign a unique row number to each row, ordered by the 'SaleAmount'. We can use ROW_NUMBER() like this:

SELECT ROW_NUMBER() OVER ( ORDER BY employee_id) row_num, name from employees;

Example 2: Using PARTITION BY

If we wanted to restart the row numbering for each 'SalesPerson', we would use the PARTITION BY clause:

Let’s see the sales table:

SELECT ROW_NUMBER() OVER (PARTITION BY SalesPerson ORDER BY SaleAmount DESC) AS RowNum 
FROM Sales;

This assigns each 'SalesPerson' a unique set of row numbers.

Function of ROW_NUMBER Function in SQL

The function of ROW_NUMBER function in SQL:

• Uniquely Identifies Rows: Assigns a unique sequential integer to each row within a specified window or partition of a result set. 
   
• Sequential Numbering: Starts numbering from 1 for the first row in each partition and increments by 1 for each subsequent row.
   
• Analytical Tasks: Commonly used for analytical tasks where the order of rows is crucial, allowing for ranking and identification.
   
• Part of Window Functions: Belongs to the category of window functions in SQL, allowing operations on a specific subset of rows.
   
• Ordered Result Sets: Enables the generation of row numbers based on a specified order, providing a means to rank or organize rows.
   
• No Inherent Meaning: The returned row numbers represent ordinal positions and do not carry any inherent meaning beyond their order within the specified window.
   
• Used for Pagination: Frequently employed for implementing pagination by skipping a certain number of rows and fetching a specified number of rows.
   
• Flexible Usage: Can be used in various scenarios, such as identifying top performers, selecting a subset of rows, or generating row-wise identifiers.

Return Types of ROW_NUMBER Function in SQL

The ROW_NUMBER() function in SQL returns an integer value representing the sequential position of a row within its partition. The exact return type is an integer or a data type that can be implicitly converted to an integer. It is important to note that the returned row numbers do not have any inherent meaning beyond their ordinal position within the specified order.

Using SQL ROW_NUMBER() for Pagination

To implement pagination using the ROW_NUMBER() function in SQL, you can use it in combination with the OFFSET and FETCH clauses, which are part of the SQL standard and allow you to skip a certain number of rows and fetch a specified number of rows. For example:

SELECT
    column1, column2, ...
FROM
    your_table
ORDER BY
    column_name
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

This query skips the first 10 rows and fetches the next 5 rows, effectively implementing pagination.

Examples of ROW_NUMBER Function in SQL

Consider a table named employees with columns employee_id, employee_name, and salary. The goal is to assign a row number based on the order of salaries in descending order.

Creating the table employees.

CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

Inserting the data into the table.

INSERT INTO employees VALUES
    (1, 'Rahul', 60000.00),
    (2, 'Drake', 75000.00),
    (3, 'Bob', 65000.00),
    (4, 'Rohit', 80000.00),
    (5, 'Jack', 70000.00);

Using ROW_NUMBER() to assign row numbers based on salary

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

Output:

Explanation:

In this example, the ROW_NUMBER() function is used within the SELECT statement. The ORDER BY salary DESC clause specifies that row numbers should be assigned based on the descending order of salaries. The result set includes the original columns (employee_id, employee_name, salary) along with the newly assigned row numbers (row_num).

How to Return a Subset of Rows Using CTE and ROW_NUMBER?

Using a Common Table Expression (CTE) along with the ROW_NUMBER() function, you can return a specific subset of rows. For example, to retrieve the top 3 highest-paid employees:

WITH RankedEmployees AS (
    SELECT
        employee_id, employee_name, salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
    FROM
        employees
)
SELECT
    employee_id, employee_name, salary
FROM
    RankedEmployees
WHERE
    rank <= 3;

In this example, the CTE RankedEmployees assigns row numbers based on the descending order of salaries, and the final query selects only those rows where the rank is less than or equal to 3, effectively retrieving the top 3 highest-paid employees.

Frequently Asked Questions

What does ROW_NUMBER do in SQL?

ROW_NUMBER() in SQL assigns a unique sequential integer to each row within a specified window or partition, often used for ranking and identification.

Is ROW_NUMBER an aggregate function?

No, ROW_NUMBER() is not an aggregate function. It is a window function used for analytical tasks, providing a unique row number to each row.

Can I use multiple columns in the ORDER BY clause of ROW_NUMBER()?

Yes, you can order by multiple columns by separating them with commas.

Does ROW_NUMBER() keep the original order of rows?

No, ROW_NUMBER() will order the rows based on the ORDER BY clause within the function.

Can ROW_NUMBER() assign the same number to different rows?

No, ROW_NUMBER() assigns a unique number to each row, unlike the RANK() function, which can assign the same rank to rows with equal values.

Conclusion

The ROW_NUMBER() function is an essential tool in the SQL toolkit, helping with data ordering, deduplication, and pagination. By understanding how to use this function, you can manage and analyze your data more effectively. Always remember, the key to efficient database management lies in understanding and appropriately using the tools at your disposal.

If you want to learn about SQL, you can read the below-mentioned articles:

• Create Table in SQL
• Basic SQL
• Data Types in SQL
• Operators in SQL
• SQL Expressions
   

We hope this article helped you in understanding database management system and the types of DBMS. You can read more such articles on our platform, Coding Ninjas Studio. You will find articles on almost every topic on our platform. Also, you can practice coding questions at Coding Ninjas to crack good product-based companies.