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)