ROW_NUMBER()

Description

ROW_NUMBER() provides a unique ordinal number for each row in the dataset, starting at one and ending with the last row in the result set. It first sorts the dataset according to the columns specified in the ORDER BY clause and then assigns each row a unique number.

Unlike the RANK() and DENSE_RANK() functions, ROW_NUMBER() gives each row a different row number when dealing with ties (i.e., two or more rows with the same value).

Syntax

  1. > ROW_NUMBER() OVER (
  2.     [PARTITION BY column_1, column_2, ... ]
  3.     ORDER BY column_3, column_4, ...
  4. )
  • The PARTITION BY clause is optional and divides the dataset into partitions; the rank is computed individually inside each partition.
  • The ORDER BY clause defines how the dataset is sorted, i.e., according to which column or columns to sort. You can specify ascending (ASC) or descending (DESC) sorting.

Examples

  1. -- Create a new table, 'SalesTable' with three fields: 'Department', 'Employee', and 'Sales'
  2. CREATE TABLE SalesTable (
  3.   Department VARCHAR(50),
  4.   Employee VARCHAR(50),
  5.   Sales INT
  6. );
  8. -- Insert data into the 'SalesTable' table; each row contains a department (Department), an employee (Employee), and their sales (Sales)
  9. INSERT INTO SalesTable (Department, Employee, Sales) VALUES
  10. ('Marketing', 'John', 1000),
  11. ('Marketing', 'Jane', 1200),
  12. ('Sales', 'Alex', 900),
  13. ('Sales', 'Bob', 1100),
  14. ('HR', 'Alice', 800),
  15. ('HR', 'Charlie', 850);
  17. -- Query the sales ranking of employees in each department
  18. -- Use the ROW_NUMBER() function to rank employees by Department (Sales)
  19. -- The ROW_NUMBER() function divides the data set into multiple partitions according to the department (Department), then sorts the sales in descending order within each partition, and assigns each row a unique row number (SalesRank )
  20. -- So, the employee with the highest sales in each department will get row number 1, the employee with the second highest sales will get row number 2, and so on
  21. mysql> SELECT
  22.     Department,
  23.     Employee,
  24.     Sales,
  25.     ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Sales DESC) as SalesRank
  26. FROM
  27.     SalesTable;
  28. +------------+----------+-------+-----------+
  29. | department | employee | sales | SalesRank |
  30. +------------+----------+-------+-----------+
  31. | HR         | Charlie  |   850 |         1 |
  32. | HR         | Alice    |   800 |         2 |
  33. | Marketing  | Jane     |  1200 |         1 |
  34. | Marketing  | John     |  1000 |         2 |
  35. | Sales      | Bob      |  1100 |         1 |
  36. | Sales      | Alex     |   900 |         2 |
  37. +------------+----------+-------+-----------+
  38. 6 rows in set (0.01 sec)