MySQL ROW_NUMBER Function

MySQL ROW_NUMBER Function

The ROW_NUMBER() function in MySQL is a window function introduced in MySQL 8.0. It assigns a unique sequential integer to each row within a partition of a result set. The numbering starts at 1 the first row in each partition.

This function is widely used for ranking rows or creating unique row identifiers within grouped data.

Syntax

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

• PARTITION BY (optional): Divides the result set into partitions. The ROW_NUMBER() function is reset to 1 for each partition.
• ORDER BY: Specifies the order in which rows are assigned their unique row numbers.

Key Characteristics

1. Unique Numbers: Each row gets a unique sequential number.
2. Reset by Partition: If the PARTITION BY clause is used, the numbering resets for each partition.
3. Sorting Dependency: The ORDER BY clause determines the order of numbering.

Examples

Example 1: Assign Row Numbers to All Rows

Given a table employees:

employee_id	department	salary
1	HR	4000
2	IT	7000
3	IT	8000
4	HR	4500
5	Finance	5000

To assign a row number to each row:

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

Output:

employee_id	department	salary	row_num
3	IT	8000	1
2	IT	7000	2
5	Finance	5000	3
4	HR	4500	4
1	HR	4000	5

• Rows are numbered based on the ORDER BY salary DESC clause.

Example 2: Reset Row Numbers by Partition

To assign row numbers within each department:

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

Output:

employee_id	department	salary	row_num
4	HR	4500	1
1	HR	4000	2
3	IT	8000	1
2	IT	7000	2
5	Finance	5000	1

• The numbering resets to 1 for each department because of the PARTITION BY department clause.

Example 3: Pagination Using ROW_NUMBER()

The ROW_NUMBER() function is often used for implementing pagination. For example, to fetch rows 2 through 4 from the sorted employee list:

SELECT employee_id, department, salary, row_num
FROM (
    SELECT employee_id, department, salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
    FROM employees
) numbered_rows
WHERE row_num BETWEEN 2 AND 4;

Output:

employee_id	department	salary	row_num
2	IT	7000	2
5	Finance	5000	3
4	HR	4500	4

• The ROW_NUMBER() function helps determine which rows fall within the specified range.

Use Cases

1. Data Ranking:

   • Assign unique ranks to rows based on specific criteria.

2. Pagination:

   • Divide large datasets into smaller pages for efficient display.

3. Duplicate Identification:

   • Use row numbers to identify and filter out duplicate rows.

4. Subset Extraction:

   • Extract specific portions of ordered data using row numbers.

Comparison with Other Ranking Functions

Function	Description
ROW_NUMBER	Assigns unique sequential numbers to rows.
RANK	Assigns ranks to rows, with ties receiving the same rank and leaving gaps in the numbering.
DENSE_RANK	Similar to RANK, but ranks are consecutive even if there are ties.
NTILE	Divides rows into a specified number of roughly equal groups and assigns a group number.

Conclusion

The ROW_NUMBER() function is a versatile tool for row-level operations in MySQL. Whether you're ranking data, implementing pagination, or managing partitions, it provides a straightforward way to add sequential numbering to your result sets. It is essential for tasks that require unique row identification or ordered data analysis.