The ROW_NUMBER function in MySQL is a window function that assigns a unique sequential integer to each row within a partition of a result set. It is commonly used in scenarios where you need to generate a unique identifier for each row based on a specific order. The function is part of the window functions introduced in MySQL 8.0, which allow you to perform calculations across a specified range of rows related to the current row.
Syntax
Here is the basic syntax of the ROW_NUMBER function:
ROW_NUMBER() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
PARTITION BY: This clause divides the result set into partitions to which the ROW_NUMBER is applied independently. If you omit this clause, the function considers the whole result set as a single partition.
ORDER BY: This clause defines the order in which the rows are numbered within each partition. You can specify one or more columns for sorting, and the optional ASC (ascending) or DESC (descending) keyword to control the sort order.
Example
Now, let’s look at an example to illustrate the usage of the ROW_NUMBER function. Suppose you have a table named employees with columns employee_id, employee_name, and salary. You want to assign a unique row number to each employee based on their salary within each department. Here’s how you can achieve that:
SELECT
employee_id,
employee_name,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as row_num
FROM employees;
In this example, the ROW_NUMBER function is used to assign a row number (row_num) to each employee within their respective department, ordered by salary in descending order. The result set will include columns for employee_id, employee_name, salary, and the calculated row_num.
Keep in mind that the ROW_NUMBER function is just one of the window functions available in MySQL. Other similar functions include RANK, DENSE_RANK, and NTILE, each serving different purposes for assigning rankings or dividing result sets into specified partitions.