What is the usage of the MySQL row_number() function?

In MySQL, the ROW_NUMBER() function is used to assign a unique number to each row in the result set, indicating the position of that row in the result set.

Here is an example of how to use the ROW_NUMBER() function:

SELECT ROW_NUMBER() OVER (ORDER BY column_name) AS row_num, column1, column2, ...
FROM table_name;

The column_name is the name of the column used for sorting, and can be specified as ascending or descending order as needed. column1, column2, … are the names of the columns to be queried.

After using the ROW_NUMBER() function, each row in the result set will have an additional column called row_num, indicating the position of that row in the result set.

For example, if there is a table called “employees” with columns “employee_id” and “employee_name”, and we want to sort the data by “employee_name” in ascending order and display the position of each employee in the sorted result, we can execute the following query:

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

After running the above query, you will receive a result set that includes three columns: row_num indicates the position of each employee in the sorting result, employee_id and employee_name indicate the ID and name of the employees.

Please note that the ROW_NUMBER() function is a new feature introduced in MySQL 8.0 and cannot be used in earlier versions of MySQL.