ROW_NUMBER()

Function Description

ROW_NUMBER() provides a unique sequence number for each row in the dataset, starting from 1 and up to the last row in the result set. It first sorts the dataset based on the columns specified in the ORDER BY clause, and then assigns a unique row number to each row.

Unlike the RANK() and DENSE_RANK() functions, ROW_NUMBER() gives each row a different line number when handling a tie (i.e. two or more rows have the same value).

Function Syntax

> ROW_NUMBER() OVER (
    [PARTITION BY column_1, column_2, ... ]
    ORDER BY column_3, column_4, ...
)

• The PARTITION BY clause is optional, which divides the dataset into multiple partitions, and calculates the line number individually within each partition.
• The ORDER BY clause defines how the dataset is sorted, that is, which column or columns are sorted according to. You can specify ascending (ASC) or descending (DESC) sorting.

Example

-- Create a new table 'SalesTable' with three fields: 'Department', 'Employee' and 'Sales'
CREATE TABLE SalesTable (
  Department VARCHAR(50),
  Employee VARCHAR(50),
  Sales INT
);

-- Insert data into the 'SalesTable' table, each row contains a department, an employee, and their sales (Sales)
INSERT INTO SalesTable (Department, Employee, Sales) VALUES
('Marketing', 'John', 1000),
('Marketing', 'Jane', 1200),
('Sales', 'Alex', 900),
('Sales', 'Bob', 1100),
('HR', 'Alice', 800),
('HR', 'Charlie', 850);

-- Check the sales ranking of employees in each department
-- Rank employees' sales by department using the ROW_NUMBER() function
-- The ROW_NUMBER() function divides the data set into multiple partitions according to the department, and then sorts it in descending order of sales (Sales) within each partition, and gives each row a unique row number (SalesRank)
-- So, the employee with the highest sales in each department will get a line number 1, the employee with the second highest sales will get a line number 2, and so on
mysql>SELECT
    Department,
    Employee,
    Sales,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Sales DESC) as SalesRank
FROM
    SalesTable;
+------------------------------------------------------------------------------------------------------------------------------
| department | employee | sales | SalesRank |
+------------------------------------------------------------------------------------------------------------------------------
| HR | Charlie | 850 | 1 |
| HR | Alice | 800 | 2 |
| Marketing | Jane | 1200 | 1 |
| Marketing | John | 1000 | 2 |
| Sales | Bob | 1100 | 1 |
| Sales | Alex | 900 | 2 |
+------------------------------------------------------------------------------------------------------------------------------
6 rows in set (0.01 sec)